Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
stdev of an array?
Hello,
I have some data that I have collected, and there is a fairly large number of data points (900). The way the data is recorded is like this: 172.5 16 172.6 16 172.7 36 and so on. I figured out how to average the values using the sumproduct() function, but how can I calculate the stdev of the values without having 900 cells filled with individual data points? Thanks, Monte |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
stdev of an array?
Hello Monte
Since a standard deviation is the square root of the variance, you need to change your variance to accomodate your data layout. In essence, a standard deviation is a weighted sum. Nothing more, nothing less. For that, you can use the SUMPRODUCT function as you may know. Hence, if your numbers are in A1:A3 and your counts are in B1:B3: =SUMPRODUCT((A1:A3-$A$8)^2,B1:B3)/(SUM(B1:B3)-1) where A8 houses the average: =SUMPRODUCT(A1:A3,B1:B3)/SUM(B1:B3) The standard deviation is calculated using the SQRT function (or raise the variance to the power 0.5) Cheers, Wigi Belgium -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Monte Milanuk" wrote: Hello, I have some data that I have collected, and there is a fairly large number of data points (900). The way the data is recorded is like this: 172.5 16 172.6 16 172.7 36 and so on. I figured out how to average the values using the sumproduct() function, but how can I calculate the stdev of the values without having 900 cells filled with individual data points? Thanks, Monte |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
stdev of an array?
Wigi,
Thanks, that seems to do the trick. I just finished up a stats class last semester so I'm familiar w/ the stdev formula; unfortunately we had to do everything on calculators so I'm still learning how to apply some of the techniques to Excel with arrays. I've got the formula working; I'll have to chew on it a bit when I have time to fully understand how everything works though. Thanks, Monte |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
stdev of an array?
You're welcome.
If you have further questions, please ask them. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Monte Milanuk" wrote: Wigi, Thanks, that seems to do the trick. I just finished up a stats class last semester so I'm familiar w/ the stdev formula; unfortunately we had to do everything on calculators so I'm still learning how to apply some of the techniques to Excel with arrays. I've got the formula working; I'll have to chew on it a bit when I have time to fully understand how everything works though. Thanks, Monte |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the STDEV of Avg? | Excel Discussion (Misc queries) | |||
how do I get STDEV() 2 sigma? | Excel Worksheet Functions | |||
STDEV | Excel Discussion (Misc queries) | |||
StDev Results | Excel Discussion (Misc queries) | |||
STDEV...HELP | New Users to Excel |