Standard Deviation from the Mean

[Pancho1990]

Hi all,

I want to see the percentage from which a certain number deviates from the average.

Which Standard Deviation formula should I use and how would it be typed in?

STD.jpg

Thank you.

[joeu2004]

I want to see the percentage from which a certain number deviates from the average.

I would not use any of the STDEV functions for that purpose. If the average is in B1 and the "certain number" is in B3, the percentage difference from the average is:

=B3/B1 - 1

or equivalently

=(B3 - B1) / B1

formatted as Percentage.

Which Standard Deviation formula should I use and how would it be typed in?

We cannot calculate the std dev based on just the average and the number of data. We must have the original data.

If the original data are in X1:X1000, the actual std dev of that data is calculated using STDEVP(X1:X1000) or STDEV.P(X1:X1000). The two functions are equivalent; MS just changed the spelling of the function name in Excel 2007.

However, if the original data are considered to be a sampling of a larger "population" of data, and you want to use the std dev of the sampling to estimate the std dev of the population, the estimated std dev of the population (not the sampling) is calculated using STDEV(X1:X1000) or STDEV.S(X1:X1000). Again, the two functions are equivalent; just a change in the spelling.

Finally, the difference between STDEV and STDEVA (or STDEVP and STDEVPA) is how numeric text is treated. Google "excel stdev versus stdeva" (without quotes) for an explanation. However, in my experiments, STDEVA does not behave as I expected. So I would not use it.

[Pancho1990]

Thank you joeu2004