Sometimes it’s important to measure the dispersion of values, and one of the best formulae is the standard deviation, which Google Sheets also provides. Here’s how to calculate the standard deviation in Google Sheets.
If you’re familiar with the math of measuring the dispersion, you’d know how important the standard deviation is. It’s the most used formula, and the other you may recognize are variance, mean, and range, which are often used to solve the same math problem.
If you’re not familiar with them, let’s think about them this way. You get two data sets. The first one has numbers that are very close to a number, and the other one is far away from each other, far away from that number. However, when you calculate each data set’s average, you’d get the same value of both.
To calculate how close those numbers to each other and how far away another one to each other as well, you would have to use the standard deviation, which is variance but with a little more complicated formula.
Another way to think about them is when you have a list of student scores. The mean is simply 80, but you couldn’t imagine how bad or how good all of the students’ scores without looking at each score.
So you need the standard deviation to know the actual distance of each score, which is 5. Great. Each student has approximately a score of either 75, 80, 85, or anything in between, with the average distance of score of 5. That’s the standard deviation.
How to calculate the standard deviation in Google Sheets
You don’t need to really understand the complete math formula of standard deviation in Google Sheets and manually calculate it on your own. Google Sheets has its own formula to do this quickly.
Here’s the formula:
STDEV(range of values)
To calculate the standard deviation in Google Sheets, simply select where you want to put the result. Type = followed by the formula. You can select the range of values once you type the open bracket.
Once you hit the close bracket, press Enter to see the standard deviation. The result will be like this.
Other than a range of values, you can also put one of these into the argument:
- the values directly instead of a range of them
- the values and the references
- filtered results (which we’ll talk about later)
Make sure you have at least two numbers or values to calculate the standard deviation in Google Sheets. Otherwise, you would get an error message.
You can also put the values to calculate it directly without selecting a range of values, like this:
Or, you can put the cell reference if you want, like this:
More, you can put the cell reference and the range at the same time, like this:
How to get the standard deviation with a filter in Google Sheets
Google Sheets has a filter function which can also be used at the same time within the standard deviation function. For example, you want to calculate the standard deviation only from a range of values containing numbers more than 75.
Then, the formula will be like this:
STDEV(FILTER(THE RANGE OF VALUES, THE CRITERIA))
The criteria are simply the requirement of what number you want to keep calculating. If you want to calculate only numbers more than 75, simply put the cell range > 75 in the criteria.
The range of values is where you want to filter. Once you have put the formula, press Enter to see the result.
It will be like this. The standard deviation is calculated from the filtered range of values.
Make sure you put the right amount of brackets and open/close them the right way. Otherwise, you would get an error message or miscalculate.
Different kinds of STDEV functions in Google Sheets
There are multiple standard deviation functions in Google Sheets and each should be used for different purposes. The one you use previously is based on a sample (STDEV).
Here are the functions and what they mean:
- STDEVP: The standard deviation based on populations
- STDEVA: The same standard deviation based on a sample but convert a text to 0 (zero)
- STDEVPA: The standard deviation based on populatioins but convert a text to 0 (zero)
Calculating the standard deviation is sometimes important to analyze your data better. Fortunately, Google Sheets also provide the function making it easy to find the standard deviation without manually calculating each set of values.
RELATED: How to Indent in Google Sheets