Standard Deviation is easy to use and understand in Excel
Calculating the Standard Deviation is simple using Excel’s in-built functions. Understanding Standard Deviation isn’t hard either. We’ll explain the five different STDEV() functions and which one you should choose.
Standard Deviation in Excel
The standard deviation measures the variation from the average or mean of a sample set of values. A higher standard deviation means the numbers vary more from the average value.
To calculate standard deviation based on a sample, that can be a cell range, array, table range etc.:
=STDEV.S(<first cell in the range>:<last cell in the range>)
(In Excel 2007 and before use STDEV() which is the same as STDEV.S() )
For example, the formula to find the standard deviation based on the sample (range of cells from A1 to E5) the formula would become as followed:
=STDEV.S(A1:E5)
Understanding a Standard Deviation
The formula behind Standard Deviation is a little complex but understanding the result isn’t that hard. Sure, there are whole Statistics course available but here’s the (overly) simplistic ‘411’.
Standard Deviation is short for Standard Deviation from the mean’. In other words, it’s a measure of how much a set of numbers varies from the average (mean).
- Low standard deviation (approaching Zero) means that values are closer to the mean/average.
- High standard deviation usually means that the values are spread out over a broader range.
That means (no pun intended) that reading a Standard Deviation only makes good sense next to the Average/Mean value as well.
Check out these four examples with just five numbers all averaging 50, one group is widely spread out but others are nearly the same.
The wide spread values have a high Standard Deviation because they are from 0 to 100.
Very narrow range has a low Standard Deviation with only values from 49 to 51.
Standard Deviation drops to zero when all the numbers are all the same.
All the Excel Standard Deviation options
Excel 2007 and before had a single STDEV() function which still works in modern Excel for Windows/Mac but has been replaced with better options.
STDEV.S()
This is the function most people will use but possibly wrongly (see Population vs Sample below).
It calculates a Standard Deviation for a sample of larger group (hence the .S ). It’s the same as the older STDEV() .
Non-number cells in the range (logical, empty, text etc) are ignored.
STDEV.P()
Added in Excel 2007 (Windows) Excel 2011 (Mac)
It calculates a Standard Deviation for an entire group or population (hence the .P ). Use this function when you have a complete list of values not a sample of a much larger group.
Non-number cells in the range (empty, text etc) are ignored.
STDEVA()
Similar to STDEV() and STDEV.S() except that Logical (True/False) cells are included in the calculation for a Sample.
Non-number cells in the range (empty, text etc) are ignored.
STDEVAP()
Similar to STDEV.P() except that Logical (True/False) cells are included in the calculation for an entire group or population.
Non-number cells in the range (empty, text etc) are ignored.
Population .P vs Sample .S – which to use?
Microsoft uses the correct statistical terms ‘Sample’ and ‘Population’ to describe the difference between the two sets STDEV functions. But it confuses us mere humans who’ve not suffered been through a Stats 101 course.
Population does NOT mean the numbers have to be about people.
In statistics ‘Population’ means a complete set of values that you want to analyze. Use STDEV.P() etc when you have:
- A full set of values for everything you want to study.
OR - A partial set of values but you’re only interested in results from that sub-set.
Sample is a part list representing a larger list. Use STDEV.S() when you have:
- A partial set of values.
AND - You want to extrapolate from that sample to generalize about the entire group (population).
In mathematical terms, the Standard Deviation formula uses a count of all the values as ‘n’. A Population like STDEV.P uses the full count ‘n’. A Sample Standard Deviation STDEV.S uses ‘ n – 1’.
Check out the sample image above to see the difference in results between STDEV.S and STDEV.P.
Example:
A list of the speeds that cars drive down a road on a single day.
To get the Standard Deviation of that day’s speeds use STDEV.P . Because you have all the values (population) for what you’re trying to analyze.
For the Standard Deviation of a whole week of car speeds, using just the single day’s results use STDEV.S . Because you have only a sample (one day) of the larger group ( a week).
We know, we know
Anyone who knows about statistics is probably grinding their teeth with anger after reading the above. It’s a severe reduction of the complexities involved in Standard Deviation and interpreting it.
We’re not trying to do a full ‘Standard Deviation 101’ course. Just point Excel users in the right direction using the STDEV() options in Excel.
At the very least, this article helps people make the right choice between STDEV.P and STDEV.S.
Ready for more? Check out Understand and use Excel’s Standard Error of the mean
Coronavirus global statistics, Excel workbook update
Easily compare a series of values with ChiTest() Excel
Using Benford’s Law magic with Excel
