How is excel's stdev formula different from what I see in wikipedia ?

[Mahurshi Akilla]

1. In wikipedia, the standard deviation for a population of 5, 6, 8,
9 is shown to be 1.58, which seems correct to me.
(http://en.wikipedia.org/wiki/Standard_deviation)

2. In excel, copy paste the following data on a new sheet, and you
get a different value (1.825)

5
6
8
9
=STDEV(A1:A4)


Could someone explain me what is going on here?

 

[Mike Tordoff]

The formula for standard deviation OF A POPULATION involves dividing the sum
of squares by n, the number of observations.

The formula for standard deviation OF A SAMPLE involves dividing the sum of
squares by n-1. This is the most common usage, and the one that Excel
calculates with the STDEV function.

Excel 2007 has a STDEVP function that gives the standard deviation of a
population.

Happy variances,

Mike Tordoff

 

[dougaj4]

1. In wikipedia, the standard deviation for a population of 5, 6, 8,
9 is shown to be 1.58, which seems correct to me.
(http://en.wikipedia.org/wiki/Standard_deviation)

2. In excel, copy paste the following data on a new sheet, and you
get a different value (1.825)

5
6
8
9
=STDEV(A1:A4)

Could someone explain me what is going on here?

It's the difference between the standard deviation of a sample and the
standard deviation of the whole population.

Look up help on =STDEV() and =STDEVP()

 

[Paul Hyett]

In microsoft.public.excel on Wed, 3 Oct 2007, Mike Tordoff

The formula for standard deviation OF A POPULATION involves dividing
the sum of squares by n, the number of observations.

The formula for standard deviation OF A SAMPLE involves dividing the
sum of squares by n-1. This is the most common usage, and the one that
Excel calculates with the STDEV function.

This is a side issue, but why would n-1 give a better indication of a
sample variance than, say, n-2, or any other variation?

 

[Mike Tordoff]

It depends upon the degrees of freedom. Wikipedia does a good job of
explaining this concept.
http://en.wikipedia.org/wiki/Degrees_of_freedom_(statistics)

Mike Tordoff

 

[Lurker]

Hmm, not really.

The standard deviation of a set of numbers is (can be?) defined as dividing
by n.

If that set of numbers is a sample drawn from a population then the standard
deviation of the set of numbers is an estimate of the standard deviation of
the population, but it is a biased estimate, see
http://en.wikipedia.org/wiki/Bias_of_an_estimator

If you want an unbiased estimator of the standard deviation of the
population you need to divide by n-1.

It's explained quite well in that Wikipedia article.

An Anonymous Lurker

It depends upon the degrees of freedom. Wikipedia does a good job of
explaining this concept.
http://en.wikipedia.org/wiki/Degrees_of_freedom_(statistics)

Mike Tordoff

 

[Paul Hyett]

In microsoft.public.excel on Thu, 4 Oct 2007, Mike Tordoff

It depends upon the degrees of freedom. Wikipedia does a good job of
explaining this concept.
http://en.wikipedia.org/wiki/Degrees_of_freedom_(statistics)

Thanks - I'll have a look.