stdev of an array?

  • Thread starter Thread starter Monte Milanuk
  • Start date Start date
M

Monte Milanuk

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
 
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,

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
 
I have two topics.

1. How do you can we calculate the standard deviation of an array with excel? The equation given in the previous post, =SUMPRODUCT((A1:A3-$A$8)^2,B1:B3)/(SUM(B1:B3)-1), is a weighted average, but does not address deviation at all. This weighted average addresses the central tendency of the data, not its dispersion.

2. What happens when the variance and standard deviation are less than 1 or exactly equal to 1? Normally the variance is larger than the standard deviation (variance=StdDev^2). It seems a little strange.

Carderock
 
I have two topics.
Click to expand...

So start a new thread instead of appending to one that is more than a
year old.

And keep your subject (title) short. Long ones are truncated by some
newsreaders and news servers.

1.  How do you can we calculate the standard deviation of an array
with excel?
Click to expand...

Try typing "standard deviation" into Excel Help. IMHO, local content
is preferred over online content.

You should discover two functions: STDEV and STDEVP. (I believe
these have different names in XL2010.) The first is used for random
samples from a larger population. The latter is used when the data is
complete, not samplings.

However, those functions do not deal with "grouped data", which was
the subject of the Jul'2008 thread.

The equation given in the previous post,
=SUMPRODUCT((A1:A3-$A$8)^2,B1:B3)/(SUM(B1:B3)-1),
is a weighted average
Click to expand...

And not even that. For a weighted average, remove -1 from the
denominator.

The discrete formulas for STDEV and STDEVP for grouped data are
similar, to wit respectively:

=SQRT(SUMPRODUCT((A1:A3*B1:B3-A8)^2)/(SUM(B1:B3)-1))

=SQRT(SUMPRODUCT((A1:A3*B1:B3-A8)^2)/SUM(B1:B3))

You can write (A1:A3)*(B1:B3) if it makes the formula clearer.

2.  What happens when the variance and standard deviation are
less than 1 or exactly equal to 1?  Normally the variance is larger
than the standard deviation (variance=StdDev^2).  It seems a little
strange.
Click to expand...

What's strange about it? Perhaps the only issue your incorrect
assertion that "normally variance is larger than std dev". As you
demonstrated, that is not true for a large range of variances between
0 and 1 inclusive.


PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
 
Back
Top