When dealing with data, we often need some metrics to understand the nature of the data. Some of these metrics include the average, the mean, mode, and standard deviation.
Google Sheets has some useful in-built formulas that you can use to perform a lot of statistical calculations.
In this tutorial, I will show you a simple formula to calculate the Standard Deviation in Google Sheets.
What is Standard Deviation?
Standard Deviation of a dataset tells you how much the data deviates from the mean.
For example, suppose you have a class of 50 students and their score in the Math exam. Now, if the mean score is 70 and the standard deviation is 10, it means that most of the student’s score is in +/- 10 range from the mean (i.e., most students has marks between 60 and 80).
While the mean gives a value that represents the entire data, the standard deviation tells how far the data is from this mean.
And what does this standard value can tell us about the data:
- A low standard deviation value tells us that most of the data points are closer to the average value of the dataset (the mean value)
- A high standard deviation value tells us that most of the data points are away from the mean (or there could be some outliers in the dataset)
The standard deviation is usually calculated by finding the square root of variance.
This deviation (also called variance) is nothing but the average of the squared differences from the mean. It can be calculated using the formula:
- μ is the mean
- Σ means the ‘sum of’
- Xi is the value of each item in the list
- N is the number of items in the list
Find it complicated? Don’t bother!
I have just shown you the formula to let you know what it means. You don’t need this when trying to calculate the standard deviation in Google Sheets.
There are in-built formulas in Google Sheets that will take care of everything.
So let’s see how you can do that!
STDEV Formula in Google Sheets
Google Sheets has the STDEV function, which takes your dataset as the input and gives you the standard deviation value.
It really is this simple! You don’t need to know any formula or be a statistics genius.
Below is the Syntax of the Standard Deviation formula in Google Sheets:
Here, the arguments can be of four types:
- A set of values
- A range of locations
- A combination of values and location ranges
- A filtered set of values
Note that there need to be at least 2 values in the arguments for the formula to work.
Using the STDEV Formula in Google Sheets to Calculate Standard Deviation
Now let me show you some examples of calculating standard deviation in Google Sheets using the STDEV formula.
Suppose you have the following dataset and you want to get the standard deviation value of these scores.
You can use the below formula:
Below is the result of this formula:
There are some more ways you use the STDEV formula in Google Sheets.
You can manually enter the values in the formula, as shown below:
This would be recommended only when you have a couple of values that can be entered manually. If you have a lot of values, it’s best to have these in Google Sheets cells and then use the range as the argument.
You can also use the individual cell reference instead of hard coding the values in the formula, as shown below:
This can be useful when you have the values in non-contiguous cells.
You can also combine a range and a cell reference (or value). For example, you can also use the below formula:
Calculating Standard Deviation with a Condition/Filter
Sometimes you may need to apply certain conditions to extract data points before calculating the Standard deviation.
In such cases, you can use the Filter formula inside your STDEV formula.
For example, if you want to calculate the standard deviation for only the students who scored above 70 (for some reason), you can type:
=STDEV(filter(location range, condition))
Here location range can be obtained by simply selecting the cells that you want to consider for the standard deviation. Condition is the criteria that qualify a cell value as eligible or not.
For example, the below formula will give you the standard deviation of only those scores that are above 70:
In the above formula, the FILTER function gives us only those values that are above 70. And then the STDEV function takes these filtered values as the input and gives the standard deviation value for it.
Rules of Using STDEV
Below are some important points to keep in mind when using the STDEV function in Google Sheets:
- STDEV usually ignores any string parameter values (or cells that include text values). Of course, it goes without saying that if all your parameter values are strings, you are going to get an error.
- The STDEV formula calculates the standard deviation for a sample. If you need to find the standard deviation for a population instead, you will need to use the STDEVP formula
- You need at least 2 number values in the arguments for the formula. Otherwise, you will get a #DIV/0! Error.
- Blank cells within your given range are ignored when calculating the Standard deviation using STDEV
Other Standard Deviation Formula in Google Sheets
- STDEVP: This is used to calculate the Standard Deviation of a population
- STDEVA: This is used to calculate the Standard Deviation while interpreting text values as 0. This could be useful when you have dashes or some text such as zero in the cell and you want these to be counted as 0.
- STDEVPA: This is used to calculate the standard deviation of a population while interpreting text values as 0
Formatting the STDEV Result
The result of the STDEV formula is a number with a lot of decimals.
If you want to change this and made this number show up less number of decimals, you need to change the number formatting for it.
Below are the steps to do this:
- Select the cell that has the result
- Click the Format option in the menu
- Hover the cursor over the Number option
- Click on the Number option.
The above steps would change the number format so you only see two decimals in the result.
I hope you found this Google Sheets tutorial useful.
You may also like the following Google Sheets tutorials: