Friends, when you are dealing with data in Google Sheets, often need some metrics to know the nature of data. Usually, these metrics include mode, mean, average, and standard deviation.
In Google Sheets, you will find some in-build formulas which you can use to perform various statistical calculations.
In this article, I am going to show you a very simple formula to calculate standard deviation Google Sheets.
What Is The Standard Deviation?
Before jumping on to the standard deviation Google Sheets formula let me tell you what is a standard deviation?
The standard deviation of a data-set tells you how much data deviate from the mean.
Example – If you have a Math exam score of 20 students, the mean score is 70 and the standard deviation is 10. This means that most students have scored in the range of +/- 10 from the means i.e., most students have got a score between 60 to 80.
Here, the mean will give a value that represents the entire data and the standard deviation Google Sheets will tell how far the data is from the mean.
We can know the following from this standard value of the data
- With a high standard deviation value, you can know that various data points are away from the mean i.e., there could be some outliers in the given data-set.
- With a low standard deviation value, you can know that various data points are closer to the average value of the mean value (data-set)
Note – Standard deviation Google Sheets is generally calculated by finding the square root of the variance.
This standard deviation (also known as variance) is the average of the squared difference from the mean. You can calculate this by formula
- μ is the mean
- Σ means the ‘sum of’
- Xi is the value of each item in the list
- N is the number of items on the list
What do you find is complicated??
Do not bother!
Here, I have just given you the formula to calculate standard deviation, however, you do not require this formula when you are calculating standard deviation in Google Sheets.
This is because the in-build formulas in Google Sheets will take care of everything. So, the standard deviation Google Sheets formula will help you in the easy calculation.
Video Source – Youtube
How To Calculate Standard Deviation On Google Sheets With STDEV Formula?
With the help of the STDEV function, you can directly get the standard deviation value of a data-set.
Yes! This is very simple, and you don’t have to remember any formula to calculate it.
So, to calculate standard deviation in sheets you need to use below syntax
In this syntax, the argument can be of four types
- A filtered set of values
- A combination of location ranges and values
- A range of locations
- A set of values
Note – To make this formula work, there should be at least 2 values in the argument.
Using STDEV Formula In Google Sheets To Find The Standard Deviation
Now it’s time to show some examples of calculating the standard deviation Google Sheets formula (STDEV).
Suppose you gave a below data-set of 20 students with scores in the Math exam and you want to get the standard deviation value of these scores.
Now, use the below formula to calculate the standard deviation Google Sheets
And, below is the result of this formula
There are various other ways to use the STDEV formula in Google Sheets.
- Manually enter the values from B2 to B21 in the formula
Note – This is recommended only if you have few values in your data-set, and can be entered easily. However, if your data set in Google Sheets is having lots of values then it will be best to use range as an argument.
- Use individual cell references instead of hard-coding values in your formula
=STDEV (B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, B12, B13, B14, B15, B16, B17, B18, B19, B20, B21)
Note – This is recommended only when you have values in non-contiguous cells
- Combine a range and a cell reference.
=STDEV (B2:B19, B20, B21)
How To Find Standard Deviation Google Sheets With A Filter/Condition?
There are multiple scenarios where you may need to apply some conditions to get data points before calculating standard deviation in Google Sheets.
In this case, you have to use a filter formula in your STDEV formula
Example: In the above data set if you want to calculate the standard deviation of the students who have scored marks above 60, then you can type
=STDEV (FILTER (LOCATION RANGE, CONDITION))
In this formula, LOCATION RANGE can be obtained by selecting the cells which you want to consider for calculating standard deviation Google Sheets.
CONDITION is the criteria that qualify a cell value as eligible or not.
Example: – In the below formula you will get the standard deviation of only those students who have scored marks above 60.
=STDEV (FILTER (B2:B21, B2:B21>60))
In this formula, the FILTER function will give you only those values that are above 60. After that, the STDEV function will take place and will give a standard deviation value for it.
What Are The Rules Of Using STDEV?
There are some important rules which you should keep in mind while using the STDEV function in Google Sheets:
- STDEV generally ignores the string parameter values (or cells that include text values). So, if all your parameter values are string, then you are going to get an error.
- In the STDEV formula, you will get a standard deviation for a sample data set. If you need to find the standard deviation for a population then you have to use the STDEVP formula instead of STDEV.
- For argument you need at least two values, else you are going to get #DIV/0! Error.
- Within a given range, if you have some blank cells then it will be ignored while calculating standard deviation using the STDEV function.
What Are The Other Standard Deviation Formulas In Google Sheets?
- STDEVP: Used to calculate the standard deviation of a population
- STDEVA: Used to calculate standard deviation while interpreting text values as 0. This is useful when you have some text such as 0 or have the dash in the cell, and you want then to calculate it as 0.
- STDEVPA: Used to calculate the standard deviation of a population interpreting text values as 0.
How To Format STDEV Results?
The result of the STDEV formula is a number with decimals.
If you want to amend this and make this number show the lesser number of decimals, then you need to amend the number formatting for it. Below are steps for the same.
- Select the cells which have the result.
- Now click the format option in the menu.
- Hover the cursor over the number option.
- Now click on the number option and you will see two decimals in the result
I hope you have understood how to use Standard deviation Google Sheets. If you have any questions, do let me know in the comment box.