The STDEV function in Google Sheets is a statistical function to calculate the standard deviation of a sample. Alternatively, you can use the STDEV.S function. Both functions show the same behavior.
I think Google Sheets keeps both the functions for compatibility with similar applications. I recommend using STDEV.S function. The syntax and usage of these functions are the same.
I am going to use the STDEV function in the examples below. You can simply replace this function with STDEV.S.
You should only use the above functions for the calculation of standard deviation if the group of numbers being evaluated is only a sample set of data of the whole population.
There is another function to calculate the standard deviation across an entire population. That’s STDEVP.
There are two more functions in Google Sheets for calculating the standard deviation of a sample. One is categorized under math (SUBTOTAL function with function numbers 7 and 107) and another is categorized under database (DSTDEV).
Database functions are different in use. So I think I may write the tutorial about the DSTDEV separately. See that here – DSTDEV Database Function in Google Sheets.
Calculate the Standard Deviation of a Sample in Google Sheets
Standard deviation helps you to find how far data values (numbers) are from their mean (average).
A low STDEV indicates the data points are close to the mean, while a high STDEV indicates the data points are spread out.
Data points Close to the Mean:
40
40
41
41
The average of the above numbers is 40.5.
Data points Spread Out:
40
40
50
9
The average of the above numbers is 17.8.
If all the data values are the same, the mean (average) will be the same. So the STDEV will return the value 0, which means zero/no deviation. You can refer to this Wiki page for more information on Standard Deviation.
Syntax and Formula Examples to the STDEV Function in Google Sheets
Syntax:
STDEV(value1, [value2, ...])
Arguments:
value1 – The first value/range of the sample.
value2 – Additional values/ranges to include in the sample. It’s optional.
STDEV Formula Examples
Example 1:
=stdev(A3:A8)
The STDEV formula returns zero as the mean of the data points are the same (26).
=AVERAGE(A3:A8)
The above formula returns 26.
Example 2:
Here in the first formula in cell C3, I have used two arguments (value 1 and value 2). The second formula in cell F3 uses the same data points in a single range and returns the same output.
Example 3:
You may sometimes want to apply some conditions to extract the data points and then find the STDEV. In such cases, you can use the Filter function in Google Sheets with STDEV.
Calculate the STDEV of the numbers >=30.
=stdev(filter(D1:D12,D1:D12>=30))
See how I have used the function Filter as a combo with STDEV.
Please note that text string and blank cells in the range will be ignored in the calculation of STDEV.
Follow the above examples to learn how to use the function STDEV/STDEV.S in Google Sheets. Enjoy!