To calculate the standard deviation of an entire population (σ), not sample, we can use the STDEVP or STDEV.P function in Google Sheets.
Earlier we have learned the use of the STDEV (STDEV.S) function (please check my function guide). That is for a sample, not for an entire population. The difference is clear, right?
As I mentioned at the very beginning, there are two functions for the same purpose. I mean STDEVP and STDEV.P for calculating the standard deviation of an entire population. The same is the case with STDEV and STDEV.S (SD of the sample).
In both these cases (entire population as well as sample), I suggest you use the latter ones, i.e. STDEV.P for the standard deviation of an entire population and STDEV.S for the standard deviation of a sample.
Because these are the relatively newer functions in Google Sheets and the names of them, especially the [dot]S and [dot]P, better reflect their usage.
At the time of writing this post, all the above functions are working fine in Google Sheets and I think it will remain so for compatibility purposes.
Before going to the syntax of the STDEV.P function in Google Sheets, let me explain how to insert the standard deviation symbol in Google Sheets?
Inserting Standard Deviation Symbol in Google Sheets
The standard deviation is represented by the Greek letter sigma (lower case). The symbol is σ
.
You can get it in Google Sheets by using the CHAR formula below.
=char(963)
In the above formula, argument 963 is the Unicode table number to insert lower case sigma in Google Sheets.
Standard Deviation of an Entire Population – Manual Calculation in Sheets
This manual calculation is meant to help you understand the standard deviation better.
The standard deviation, in statistics, is a measure of the amount of variation of a set of values. That means how values are spread out.
How to read the standard deviation output then?
- Low σ – The values tend to be close to the average/mean of the set.
- High σ – The values are spread out over a wider range.
Calculation Without Using the STDEVP or STDEV.P Function in Google Sheets
Sample Data:
160 | 140 | 172 | 130 | 90 |
Let me show you how to calculate it in 3 steps without using the STDEVP function in Google Sheets.
Step 1: Finding Mean (Average).
=(160+140+172+130+90)/5
Result: 138.4
Step 2: Find the Difference of Each Value from Mean (Average), then Square It.
=(160-138.4)^2+(140-138.4)^2+(172-138.4)^2+(130-138.4)^2+(90-138.4)^2
Result: 4011.2
Step 3: Average the Sum of Squared Difference which is Variance – σ^2
.
=4011.2/5
Result: 802.24
Then finally find the square root;
σ = √802.24
Result: 28.32
We can avoid all the above steps by using the STDEVP / STDEV.P function in Google Sheets.
STDEVP / STDEV.P Function Syntax
Syntax:
STDEVP(value1, [value2, ...])
STDEV.P(value1, [value2, ...])
Arguments:
value1 – The first value or array/range of the population.
value2 – Additional values or arrays/ranges to include in the population.
Formula Example
In this example, I am going to use the same values used in our manual calculation above so that we can match the result.
The numbers are in B3:B7 and here are the STDEVP and STDEV.P formulas.
=stdevp(B3:B7)
=stdev.p(B3:B7)
As already said, the standard deviation of an entire population is the square root of the variance of the entire population.
I have detailed the same under the subtitle “Entire Population without the STDEVP or STDEV.P Function in Google Sheets” above.
That means we can use the below formula as an alternative to STDEVP.
=sqrt(varp(B3:B7))
Usage Notes
The below points are applicable depending on the use of arguments in the function.
- When Using the values as a cell/range reference;
- Blank cells, Boolean values, or text in the array/range reference are ignored. That means only numbers are counted that include 0.
- Error-values cause the formula to return an error.
- When using values directly in the formula.
- STDEVP/STDEV.P will return an error if any of the values are text or error values.
- Boolean values (TRUE/FALSE) evaluate to 1 or 0.
- The text representations of numbers like “100” instead of 100 will be counted.
Resources
- Mean and Standard Deviation Straight Lines on a Column Chart in Google Sheets.
- Standard Deviation – DSTDEV Database Function in Google Sheets.
- How to Use the VAR Function in Google Sheets.
- How to Use the STDEV Function in Google Sheets.
- Google Sheets Average Function [Advanced Tips and Tricks].
- How to Use the TRIMMEAN Function in Google Sheets.
- How to Use the HARMEAN Function in Google Sheets.
- GEOMEAN for Geometric Mean Calculation in Google Sheets.