Monthly Data, need quarterly Stdev
 

My monthly data are in a column. I am currently using B1=Stdev(A1:A3)....
then selecting 3 vertical cells (ie: B1:B3) then dragging it down. But that
leaves each quarterly Stdev 2 spaces apart. How would I do it so that the
quarterly Stdev organized in 1 spaceless column?

Monthly Data, need quarterly Stdev
 

J @ Y wrote:
My monthly data are in a column. I am currently using B1=Stdev(A1:A3)....
then selecting 3 vertical cells (ie: B1:B3) then dragging it down. But that
leaves each quarterly Stdev 2 spaces apart. How would I do it so that the
quarterly Stdev organized in 1 spaceless column?

I do not agree with your method of computing quarterly std dev. But to
answer your Excel question, one way is to enter the following array
formula into B1 (use ctl-shift-Enter) and copy down appropriately:

=STDEV( OFFSET($A$1:$A$3, 3*(ROW(B1)-ROW($B$1)), 0) )

If your std dev formulas truly begin in row 1, you could write simply
"row(B1)-1" instead of "row(B1)-row($B$1)". The latter generality
permits you to begin the table in any row, not just row 1.

Monthly Data, need quarterly Stdev
 

J@Y -

Select a single cell, i.e., B1. Then drag down.

- Mike
http://www.mikemiddleton.com

"J@Y" wrote in message
...
My monthly data are in a column. I am currently using B1=Stdev(A1:A3)....
then selecting 3 vertical cells (ie: B1:B3) then dragging it down. But
that
leaves each quarterly Stdev 2 spaces apart. How would I do it so that the
quarterly Stdev organized in 1 spaceless column?

Monthly Data, need quarterly Stdev
 

Thanks for the solution. May I ask what your method of calculating quarterly
STdev is?

" wrote:

J @ Y wrote:
My monthly data are in a column. I am currently using B1=Stdev(A1:A3)....
then selecting 3 vertical cells (ie: B1:B3) then dragging it down. But that
leaves each quarterly Stdev 2 spaces apart. How would I do it so that the
quarterly Stdev organized in 1 spaceless column?

I do not agree with your method of computing quarterly std dev. But to
answer your Excel question, one way is to enter the following array
formula into B1 (use ctl-shift-Enter) and copy down appropriately:

=STDEV( OFFSET($A$1:$A$3, 3*(ROW(B1)-ROW($B$1)), 0) )

If your std dev formulas truly begin in row 1, you could write simply
"row(B1)-1" instead of "row(B1)-row($B$1)". The latter generality
permits you to begin the table in any row, not just row 1.

Monthly Data, need quarterly Stdev
 

The offset equation returns an range(ie: $A$1:$A$3), but if I put a 1+ in
front of the Offset function (=STDEV(1+ OFFSET($A$1:$A$3,
3*(ROW(B1)-ROW($B$1)), 0) ), it gives me a #Value error. Going through
Formula Evaulation, I noticed that the offset function does not return a
range anymore if the 1+ is added. Why is that?



" wrote:

J @ Y wrote:
My monthly data are in a column. I am currently using B1=Stdev(A1:A3)....
then selecting 3 vertical cells (ie: B1:B3) then dragging it down. But that
leaves each quarterly Stdev 2 spaces apart. How would I do it so that the
quarterly Stdev organized in 1 spaceless column?

I do not agree with your method of computing quarterly std dev. But to
answer your Excel question, one way is to enter the following array
formula into B1 (use ctl-shift-Enter) and copy down appropriately:

=STDEV( OFFSET($A$1:$A$3, 3*(ROW(B1)-ROW($B$1)), 0) )

If your std dev formulas truly begin in row 1, you could write simply
"row(B1)-1" instead of "row(B1)-row($B$1)". The latter generality
permits you to begin the table in any row, not just row 1.

Monthly Data, need quarterly Stdev
 

J @ Y wrote:
Thanks for the solution. May I ask what your method of calculating quarterly
STdev is?

J@Y wrote previously:
My monthly data are in a column. I am currently using B1=Stdev(A1:A3)....
then selecting 3 vertical cells (ie: B1:B3) then dragging it down. But that
leaves each quarterly Stdev 2 spaces apart. How would I do it so that the
quarterly Stdev organized in 1 spaceless column?

If A1:A3 are "monthly data", then stdev(A1:A3) is the "monthly std dev"
-- that is, the std dev of the monthly data, not the quarterly data.
If you want a quarterly std dev, you need "quarterly data". Exactly
what that means depends on what your "monthly data" are. For example,
if they are the number of sales per month, then for quarterly
statistics, I would sum every 3 months and take the std dev of the
quarterly sums. If the "monthly data" are growth rates, I would
compute the 3-month growth rate and take a std dev (arithmetic or
geometric) of the quarterly rates.

Moreover, you are computing a std dev of only 3 data points. Usually
that is not sufficient data to determine a std dev -- that is, a std
dev that you intend to use for other statistical inferences.
Arguably, the minimum number of data points needed depends on the size
of the confidence interval that you want around the average and on the
dispersion of the data. (Yes, the std dev is a measure of dispersion;
so yes, that is a circular specification -- intentionally so.) So 3
data points might work in some circumstances. But usually not.

HTH.

Monthly Data, need quarterly Stdev
 

J @ Y wrote:
The offset equation returns an range(ie: $A$1:$A$3), but if I put a 1+ in
front of the Offset function (=STDEV(1+ OFFSET($A$1:$A$3,
3*(ROW(B1)-ROW($B$1)), 0) ), it gives me a #Value error.

I suspect you forgot that this is an array formula. Select the cell,
edit it or press F2, then type ctl-shift-Enter.

That should eliminate the #VALUE! error. But that does not mean the
formula will return what you intended.
Computer geeks like me call this GIGO ("garbage in, garbage out").

Why are you adding "1+" to the OFFSET() result? That is, what do A1:A3
contain (i.e. what is the form of the "monthly data"); and what do you
expect by adding 1 to them?

I am not saying necessarily that you are doing anything wrong. But
since your comments reflect a misunderstanding of what OFFSET() does
exactly, I wonder if your "1+" is simply building upon that
misunderstanding.

Monthly Data, need quarterly Stdev
 

Errata....

I wrote:
since your comments reflect a misunderstanding of what OFFSET() does exactly

I misspoke.

I wonder if your "1+" is simply building upon that misunderstanding

Still curious, just to be sure there is no misunderstanding.