The best way to calculate standard deviation in Google Sheets is to use the STDEV function which is already available there. But there are multiple functions which are available to calculate the standard deviation. Each function has its own usage.
What is Standard Deviation?
Standard deviation is a way to tell how spread-out numbers are in a group. Let’s say you have a bunch of balloons with different numbers representing ages, scores, or prices. If most of the balloons are close to each other and clumped around the average number, the standard deviation is small, so there’s not much difference between the numbers.
But if the balloons are all over the place, far from the average, the standard deviation is large, showing a wide range of numbers.
Calculate Standard Deviation in Google Sheets
Let’s say you want to analyze the test scores of a class with the standard deviation to understand how much variation there is from the average score.
Here’s how to calculate standard deviation using the STDEV.S for students, broken down into simple steps:
- First, add the numbers you want to analyze into a column in Google Sheets. For example, we have student data in the range A1:B8, and their scores are in column B.
- Then, select a cell where you want the standard deviation to result to enter. Let’s say this cell is D2.
- Now, in cell B9, type the formula =STDEV.S(B2:B8). This is the range where we have the students’ scores to test for standard deviation.
- In the end, press Enter. Google Sheets will calculate and return the standard deviation of the scores in the range B2 to B8.
=STDEV.S(B2:B8)
Here, the standard deviation of 9.20 points suggests that the scores generally deviate from the mean by about 9.20 points. This indicates a moderate variability among the scores.
Assuming the scores are somewhat normally distributed, you can use the SD to estimate that approximately 68% of the scores lie within one SD of the mean (mean ± 9.20).
About 95% of scores lie within two standard deviations (mean ± 2 * 9.20), and about 99.7% within three standard deviations (mean ± 3 * 9.20).
Calculate Standard Deviation with a Condition
You can calculate the standard deviation with a condition in Google Sheets using the STDEV.S function combined with the FILTER function. This allows you to apply specific criteria to your data before calculating
=STDEV.S(FILTER(C2:C8,B2:B8="F"))
In the above example, we have a list of students with scores and their gender, and we need to get the standard deviation only for “Females”.
This means it filters only column C scores that match “F” entries in column B. Once these values are selected, the STDEV function calculates the standard deviation of these filtered values.
This means it filters only column C scores that match “F” entries in column B. Once these values are selected, the STDEV function calculates the standard deviation of this filtered values.
The standard deviation tells you how much the values in this filtered list of females deviate from their average, giving you a measure of variability or spread among the values.
Other Function that can be used for Standard Deviation
Well, there are two major functions, or I’d say there are two ways to calculate in Google Sheets, based on the type of the calculation you require.
- STDEV.P – It calculates the standard deviation using the entire population. It’s used when you have all the data points from the group you’re studying and want to determine the exact spread.
- STDEVA – It is like STDEV.S, but it includes text and logical values (TRUE/FALSE) in the calculations. Text is treated as 0, TRUE as 1, and FALSE as 0. This is useful for datasets with mixed types where no n-numeric values are there.
- STDEVPA – This is an all-inclusive function that calculates the standard deviation for an entire population, counting text (as zero) and logical values (TRUE as one, FALSE as zero). It is the most comprehensive standard deviation function, ideal for detailed data analysis.
- STDEVP – The counterpart to STDEVA, STDEVP calculates the standard deviation, including all values (numeric, text, and logical). It’s used when a complete data set is available, and non-numeric data in the source.
Population vs. Sample Data
Choosing the correct function impacts the accuracy of your analysis. Given the same set of data, the standard deviation of a sample is generally larger than that of a population because the sample standard deviation corrects for the fact that it only represents a subset.
- Population refers to the complete set of data or observations that could be made concerning a particular group or category. The entire dataset for which you’re trying to get the standard deviation.
- The sample is a subset of the population chosen for the practicality of observation. The sample is used to estimate the characteristics of the entire population.