How to set up this array formula?
 

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?

How to set up this array formula?
 

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.

--
Biff
Microsoft Excel MVP


"joeu2004" wrote in message
ps.com...
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?

How to set up this array formula?
 

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:"&CO UNTA($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.



On Nov 4, 6:06 am, joeu2004 wrote:
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?

How to set up this array formula?
 

On Nov 4, 9:33 am, "T. Valko" wrote:
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.

How to set up this array formula?
 

"joeu2004" wrote in message
oups.com...
On Nov 4, 9:33 am, "T. Valko" wrote:
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.


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.

--
Biff
Microsoft Excel MVP