When supervising the performance of your business you need to set a way of flagging unusual events such as unusually high sales, unusually high complaints, etc. One way to do so is by calculating the standard deviation.
For a given set of values, the standard deviation measures how much a specific value varies from the mean or average of the set of the values. This is one of basic but important measures of variation of the values of a given dataset.
The Google Sheets STDEV function is used to calculate the standard deviation of a dataset.
=STDEV(range)
Where:
range - contains the set of values whose standard deviation you want to calculate.
Here are the steps in using the function to calculate the standard deviation:
For small ranges that fit in your screen, you can drag through the range to select it. For large ranges it is best to determine their corresponding reference and type it inside the function. You can also use named ranges here.
You have now added the standard deviation of the given range of values!
You can check the screencap below:
It is possible to filter the values in the range before calculating the standard deviation. Use the Google Sheets FILTER function in the following manner:
=STDEV(FILTER(range,condition))
Where:
range - is the range of the cells that contain the values you want to process
condition - is the condition for filtering the data to include in the function
Using the same example as in the previous section, let’s say we want to only include the values more than 0.92. Follow these steps:
For small ranges that fit in your screen, you can drag through the range to select it. For large ranges it is best to determine their corresponding reference and type it inside the function. You can also use named ranges here.
The condition should include the target range. For example, if you only want to include values from A2:A11 that are greater than 0.92, you should set the following condition:
A2:A11>0.92
The condition should not be enclosed in double quotes.
Voila! You can see that the FILTER works well as the output value is different from the plain function.
You can check the screencap below:
Google Sheets has other types of standard deviation formulas. They are the following:
STDEVP: Calculating the standard deviation of a population
STDEVA: Calculating the standard deviation of a sample, setting text values to 0
STDEVPA: Calculating the standard deviation of a population, setting text values to 0
Google Sheets offer these formulas because statisticians make a distinction between sample and population. In statistics, population refers to the entire set of all data that describe a group. Most of the time, however, gathering data for the entire population is impractical. Thus, statisticians instead focus on the sample.
This is the origin of the idea of sampling for surveys: they try to gather a realistic size of people that can reliably reflect the sentiments of the entire population.
This is similarly true to business analytics: it can easily be impractical for you to churn the entire data to produce the metrics you need. Instead, you run analytics tools on a portion of the data that you have. So, by default, the Google Sheets STDEV is for samples. Most of the time, it is the appropriate function for the amount of data that you process.
Use our renewal tracking software to easily track renewals from your spreadsheet in just a few clicks.
If you enjoyed this article, you might also like our article on how to round numbers in Google Sheets or our article on how to divide in Google Sheets.
If you want to learn how to extract names from email addresses in Google Sheets, we also suggest checking out our detailed guide.
