# Standard deviation in Excel.

1. Jan 21, 2010

### ssd

By definition standard deviation has divisor 'N' .

But in Microsoft Excel the "STDEV" function gives 'N-1' as divisor.
This may not be major issue to many of the people. But the command "STDEV"
makes many users feel that this is standard deviation, i.e. it has divisor 'N'. Misleading...is not it?

2. Jan 21, 2010

### EnumaElish

3. Jan 21, 2010

In texts you will see the one with divisor N defined as the population standard deviation: the one with divisor N-1 is referred to as the sample standard deviation.

There is no single "standard deviation formula" - the appropriate formula depends on context.

4. Jan 21, 2010

### CRGreathouse

Excel has STDEV, the sample standard deviation, and STDEVP, the population standard deviation. Usually you want the sample standard deviation, which is why the commands are named as they are.

I'm pretty sure this is all in the Excel help.

5. Jan 23, 2010

### ssd

At the very outset of this reply let me say that all of you are correct from your
respective point of view. But my point was a little different which EnumaElish has got almost correctly.
To clarify my point I will say a word or two.

True. You rightly got it: "sometimes" not in general- or I may say, not commonly.

Point well taken.

In the first step of stating the descriptive measures of dispersion all the text books, as I have gone through
( ex: attachment -1), use a single and unique "standard deviation formula" (and no context dependent definition). And this is done much before coming to the chapters of "point estimation" or "sample survey" (where we start talking about "expectation/unbiasedness" or "population/sample".

Then again, as learnt from text books( attachment-2), the sample variance (also) have divisor
n, not n-1). Hence it is stated, sample variance is not unbiased estimator of population variance.

attachment -1

attachment-2

Last edited by a moderator: Apr 24, 2017
6. Jan 23, 2010

The standard deviation with the divisor of n is the MLE when the Gaussian model is assumed and both $$\mu$$ and [/tex] \sigma [/tex] are unknown. (It is a function of the variance, the MLE of $$\sigma ^2$$, so is the MLE of the standard deviation. The sample variance with the divisor of n -1 comes from the unbiased estimator of variance.

But: this expression
$$\frac 1 n \sum_{i=1}^n (x_i-\bar x)^2,$$

are also given as formulas for population variance and sample variance, respectively, for discrete populations, since in this case the population mean is defined as

$$\mu = \sum 1 n \sum_{i=1}^n$$

and so is identical in form to the arithmetic mean.

So, when you use the variance with n in the denominator, you must be clear whether you are dealing with a sample or population: the interpretation differs. That is the context.

7. Jan 23, 2010

### ssd

Agree. But my point differs a little from yours.

What if you are given with 20 no.s and asked to find their mean, variance, skewness and kurtosis...... without any reference to population/estimation. Remember what we did in our stating course of descriptive statistics. What I am trying to say is that, there are situations when one can look upon variance as a purely descriptive measure (of dispersion). And this measure itself is meaningful some times (apart from the fact that this measure will be used for inferential data analysis).

8. Jan 24, 2010