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

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

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

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