Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Standard deviation in Excel.

  1. Jan 21, 2010 #1

    ssd

    User Avatar

    By definition standard deviation has divisor 'N' .
    sdmjgp.png
    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. jcsd
  3. Jan 21, 2010 #2

    EnumaElish

    User Avatar
    Science Advisor
    Homework Helper

  4. Jan 21, 2010 #3

    statdad

    User Avatar
    Homework Helper

    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.
     
  5. Jan 21, 2010 #4

    CRGreathouse

    User Avatar
    Science Advisor
    Homework Helper

    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.
     
  6. Jan 23, 2010 #5

    ssd

    User Avatar

    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.
    That is why I used the term "misleading" instead of "bugged".

    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.

    30nf95w.jpg
    attachment -1

    2rdu3h5.jpg
    attachment-2
     
    Last edited by a moderator: Apr 24, 2017
  7. Jan 23, 2010 #6

    statdad

    User Avatar
    Homework Helper

    The standard deviation with the divisor of n is the MLE when the Gaussian model is assumed and both [tex] \mu [/tex] and [/tex] \sigma [/tex] are unknown. (It is a function of the variance, the MLE of [tex] \sigma ^2 [/tex], 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
    [tex]
    \frac 1 n \sum_{i=1}^n (x_i-\bar x)^2,
    [/tex]

    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

    [tex]
    \mu = \sum 1 n \sum_{i=1}^n
    [/tex]

    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.
     
  8. Jan 23, 2010 #7

    ssd

    User Avatar

    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).
     
  9. Jan 24, 2010 #8

    statdad

    User Avatar
    Homework Helper

    "without any reference to population/estimation" - I can't imagine why you you would want any set of statistics without wondering about any descriptive or inferential purpose (unless it is simply an exercise in calculation) - but so be it.
     
  10. Jan 26, 2010 #9

    ssd

    User Avatar

    Right. I was more concerned about my students who are going through the initial stages of learning descriptive statistics. Most of them (if not all) use excel for their course work.
     
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook