Standard Deviation in Excel, SigmaPlot and SPSS

#1
Hi all,

I have found this forum through a google search and this is my first post. I usually read and ocassionally post on an astronomy forum (http://www.bautforum.com/). I have also asked this question on their Q&A section.
I thought that maybe there might be more specialized knowledge on this forum regarding this particular subject.
Thank you in advance for taking time to ponder my questions.


I have read that the way Excel calculates standard deviation
=STDEV(A1:A9)
is not "proper". This function uses the formula
stdev = sqrt( (n * SUMof (samples^2) - SUMof(samples)^2) / (n* (n-1) )
In excel's help it is described as the "unbiased" method.

The "proper" way according to some sources is:
stdev = sqrt( 1/n SUMof(( sample - mean)^2) )
This can be achieved in Excel by using
=STDEVP(A1:A9)
I've also read that excel should not be used for statistical purposes at all. Now I am being introduced to actual statistical packages such as Sigmalpot and SPSS. And I've found that they calculate standard deviation the "Excel way" (at least in the cases I have observed).

Should I be concerned?
I've got a feeling that both formulas might have their merits but should be used under different circumstances. If this is right, when should I use which formula?

I did some reading but haven't really reached a conclusion for myself yet. Here are some of the links I've looked at:

http://www.maryparker.org/excel/stdev.htm
http://www.cs.uiowa.edu/~jcryer/JSMTalk2001.pdf
http://en.wikipedia.org/wiki/Standard_deviation (plus its talkpage)

Thanks for your time!
 

mp83

TS Contributor
#2
All, this arrise by the choice of denominator.Choosing n instead of n-1. The latter is the unbiased. In large samples though it makes little sense what you ckoose (the biased one is consistent), so most packages use the simpler one, that is n.

In small samples you should always use the unbiased one

Hope it helps