How to set up this array formula?

[joeu2004]

I want to compute the standard deviation of the averages of several
random samples of data. Suppose the random samples are in A1:A10,
B1:B10, C1:C10, etc.

Of course, I could compute the averages in row 11 (e.g.
=AVERAGE(A1:A10) in A11), then compute STDEV(A11:H11) if I have 8
samples. But I would like to avoid storing the intermediate averages.

Ostensibly, I could compute
STDEV(AVERAGE(A1:A10),AVERAGE(B1:B10),...). But that works only for
up to 30 samples.

I would like to have an array formula that effectively computes
STDEV({AVERAGE(A1:A10), AVERAGE(B1:B10),..., AVERAGE(ZZ1:ZZ10)}).

How can I do write that array formula?

 

[T. Valko]

Try something like this array formula:

=STDEV(SUBTOTAL(1,OFFSET(A1:ZZ10,,COLUMN(A1:ZZ10)-COLUMN(A1),10,1)))

I don't have Excel 2007 so I could only test up to column IV.

 

[iliace]

Well, you could define a multi-cell array formula that returns all the
averages...

=AVERAGE(INDIRECT("R1C"&ROW(INDIRECT("1:"&COUNTA($1:$1)))&":R10C"&ROW(INDIRECT("1:"&COUNTA($1:$1))),FALSE))

If you array-enter this into a column, it will return each average
respectively.

However, this *will not* work within an STDEV() function, like so:

=STDEV(AVERAGE(INDIRECT("R1C"&ROW(INDIRECT("1:"&COUNTA($1:$1)))&":R10C"&ROW(INDIRECT("1:"&COUNTA($1:$1))),FALSE)))

It also *will not* work as a defined name, such as:

MyAverages=AVERAGE(INDIRECT("R1C"&ROW(INDIRECT("1:"&COUNTA($1:$1)))&":R10C"&ROW(INDIRECT("1:"&COUNTA($1:$1))),FALSE))
=STDEV(MyAverages)

I can't think of a different way off top of my head, sorry.

 

[joeu2004]

Try something like this array formula:
=STDEV(SUBTOTAL(1,OFFSET(A1:ZZ10,,COLUMN(A1:ZZ10)-COLUMN(A1),10,1)))

I don't have Excel 2007 so I could only test up to column IV.

That probably works for me, although I haven't tested it myself. I
have Excel 2003. I chose "ZZ" stylistically to mean "more than 30
columns", without thinking about whether or not the column actually
exists. I could have avoided the confusion by putting my samples in
rows instead of columns.

PS: I don't like to use SUBTOTAL() because of its poor "readablility"
-- the use of function numbers (1 or 101 in this case) to denote the
underlying function. If there is a reasonable solution that avoids
SUBTOTAL(), I'm still interested. But arguably, SUBTOTAL() might have
been invented for exactly this type of problem

Thanks.

 

[T. Valko]

That probably works for me, although I haven't tested it myself. I
have Excel 2003. I chose "ZZ" stylistically to mean "more than 30
columns", without thinking about whether or not the column actually
exists. I could have avoided the confusion by putting my samples in
rows instead of columns.

PS: I don't like to use SUBTOTAL() because of its poor "readablility"
-- the use of function numbers (1 or 101 in this case) to denote the
underlying function. If there is a reasonable solution that avoids
SUBTOTAL(), I'm still interested. But arguably, SUBTOTAL() might have
been invented for exactly this type of problem

Thanks.

If there is a reasonable solution that avoids
SUBTOTAL(), I'm still interested.

None that I can think of other than what you already said you wanted to
avoid, using intermediate values.