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?

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?

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.

"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

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?