Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert monthly data into quarterly data? | Excel Discussion (Misc queries) | |||
Data Matching Button for MS Excel 2007 or later | Excel Worksheet Functions | |||
Collecting weekly and monthly totals from daily data | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions |