In this tutorial, we can learn how to use the STDEVPA Function in Google Sheets.
There are two types of standard deviations (SD). They are population standard deviation and sample standard deviation.
If you want to calculate as per type # 1, you can use the STDEVPA function in Google Sheets.
As far as I know, to calculate these two types, there are ten functions (five each) in Google Sheets, and they are as per the table below.
Table Contain the List of All the Standard Deviation Functions in Google Sheets:
Serial Number | Function Name | Type | Remarks |
1 | DSTDEV | Population Sample | Database function |
2 | DSTDEVP | Population | Database function |
3 | STDEV | Population Sample | |
4 | STDEVS | Population Sample | |
5 | STDEVA | Population Sample | Setting text to zero |
6 | STDEVP | Population | |
7 | STDEV.P | Population | |
8 | STDEVPA | Population | Setting text to zero |
9 | SUBTOTAL(7 or SUBTOTAL(107 | Population Sample | STDEV – skips hidden/filtered out rows |
10 | SUBTOTAL(8 or SUBTOTAL(108 | Population | STDEVP – skips hidden/filtered out rows |
You can find most of them listed in my Google Sheets Functions Guide.
Among the five functions for population standard deviation, the STDEVPA function stands out from the rest as it evaluates text strings as 0 (zero).
As a side note, to know the other four functions for population SD, please check the ‘Type’ and ‘Remarks’ columns of the above table.
Syntax and Arguments of the STDEVPA Function in Google Sheets
Syntax:-
STDEVPA(value1, [value2, …])
Arguments:-
value1 – The first value or array of the population.
value2 – Additional values or arrays to include in the population.
Features:-
The below points are applicable when an argument is an array or array/range reference.
The STDEVPA function in Google Sheets evaluates-
- STDEVPA evaluates each text value as 0 (zero).
- Boolean TRUE evaluates as 1 (one), whereas FALSE evaluates as 0 (zero).
- Empty cells are ignored.
- Error-values in the array or array reference cause errors in the STDEVPA formula output in Google Sheets.
You can get to practically know the features with the help of my examples below.
Formula Examples
Below I have included ten formula examples to the STDEVPA function in Google Sheets.
That may help you understand how the function evaluates texts, Boolean values, and blank cells in the array or array reference.
The following formula is in cell A12 for the range A2:A11.
=STDEVPA(A2:A11)
I have copied it for the other columns by dragging the cell A12 fill handle to its right-hand side.
What happens when you enter values directly within the STDEVPA formula (into the list of arguments) in Google Sheets?
- STDEVPA will return
#VALUE!
error, if any of the entered values are text. You can test it as follows.
=stdevpa(5,10,"Test String")
- The text representations of numbers will be counted.
E.g.:-
=stdevpa(5,10,"5")
- Boolean TRUE evaluates as 1 where as FALSE evaluates as 0 (zero). The formula
=stdevpa(5,10,TRUE)
is equal to using=stdevpa(5,10,1)
.
- Error values in the array cause errors.
Conclusion
The difference between STDEVP and STDEVPA functions in Google Sheets lies in evaluating the Boolean values (logical TRUE and FALSE) and text strings.
If you don’t want to consider them, use the former function, else the latter.
We can calculate the population standard deviation with the help of some other functions (which are not listed in the above table) and operators in Google Sheets.
If you go through them below, you can understand SD.
Let’s consider the values in A2:A11 (please refer to the image above).
Here are the steps to follow.
- In cell C2 insert
=average(A2:A11)
to return the average of the numbers in the array A2:A11. - Using
=ArrayFormula(sum((A2:A11-C2)^2))
in cell D2, we can calculate the difference of each value from the number in cell C2, i.e., average, and square it. - Find the variance using
=D2/count(A2:A11)
in cell E2. - Find the square root of the value in E2. You can use
=sqrt(E2)
for that and check the output.
That’s all about the STDEVPA function in Google Sheets. Thanks for the stay. Enjoy.
Related:- Mean and Standard Deviation Straight Lines on a Column Chart in Google Sheets.