Statistical functions on non-consequtive columns

  • Thread starter Thread starter Frode
  • Start date Start date
F

Frode

Hi,

I have several data tables, where every second column is an index and the
other columns uses the index to fetch the corresponding data. If any data is
invalid, #N/A is returned, so that the data can be shown in graphs (used ""
instead of #N/A in the beginning, but this made the graphs very noisy,
dropping to zero).
For each column of data the MIN, MAX and AVG is given by an array formula,
e.g.
{=AVERAGE(IF(ISNUMBER(B11:B754);B11:B754;""))}.
So far so good.
The problem is to give the average of each row in the tables, the RowAVG
below, since the data are in non-consequtive columns.
I would be greatful to receive a solution to this problem, without having to
duplicate the Data columns into a similar table but without the Index
columns.

Index1 Data1 Index2 Data2 Index3 Data3 RowAVG
1 10 1 20 1 30
20
2 15 2 #N/A 2 35
25
3 35 3 20 3 65
40

ColAVG 20 20 43.3
ColMIN 10 20 30
 
One way
=SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),A2:G2)/SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),--(ISNUMBER(A2:G2)))
Fill it down!
--
Regards!
Stefi



„Frode†ezt írta:
 
Sorry, this is the correct version for AVG:

=SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),A2:F2)/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(ISNUMBER(A2:F2)))


--
Regards!
Stefi



„Stefi†ezt írta:
 
Thanks Stefi, but this returned #N/A for the 2nd row where one of the data
fields was #N/A. It worked for the rows where all data fields were valid.
 
Hi again,

Got it working if bringing in the ISNUMBER check twice. I don't know if this
is the best way, but it works. Thanks for the hint.

{=SUMPRODUCT(--(MOD(COLUMN(FM3:FR3);2)=0);IF(ISNUMBER(FM3:FR3);FM3:FR3;""))/SUMPRODUCT(--(MOD(COLUMN(FM3:FR3);2)=0);(IF(ISNUMBER(FM3:FR3);1;0)))}
 
Sorry, I forgot to mention that these are array formulae to be confirmed with
Ctrl+Shift+Enter. This is a bit reformatted formula:

for Avg
=SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),IF(ISNA(A2:F2),0,A2:F2))/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(NOT(ISNA(A2:F2))))


for Max:
=MAX(--(MOD(COLUMN(A2:F2),2)=0),IF(ISNA(A2:F2),0,A2:F2))

Still thinking on Min.

--
Regards!
Stefi



„Frode†ezt írta:
 
For Min (also array formula):

=MIN(IF(MOD(COLUMN(A2:F2),2)=0,1,6.022*10^23)*IF(ISNA(A2:F2),6.022*10^23,A2:F2))


--
Regards!
Stefi



„Stefi†ezt írta:
 
Thank you very much Stefi. I've now got all my functions. I didn't get it
right with your MAX function, but I just modified your MIN function like this:
{=MAX(IF(MOD(COLUMN(FM3:FR3);2)=0;1;0)*IF(ISNA(FM3:FR3);0;FM3:FR3))}
 
Back
Top