How to Calculate Standard Deviation in Google Sheets

What is Standard Deviation?

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. 

Google Sheets STDEV 

The Google Sheets STDEV function is used to calculate the standard deviation of a dataset.

Syntax

=STDEV(range)

Where

range 

Contains the set of values whose standard deviation you want to calculate

Calculate Standard Deviation with STDEV

Here are the steps in using the function to calculate the standard deviation:

Step 1: Type =STDEV(

Step 2: Select the range

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.

Step 3: Press Enter

You have now added the standard deviation of the given range of values!

You can check the screencap below:

Calculate with a filter

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:

Step 1: Type =STDEV(FILTER(

Step 2: Select the range

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.

Step 3: Type the condition

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. 

Step 4: Press Enter

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:

Other types of Standard Deviation formulas

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.