Standard deviation is one of the main measures of dispersion in a dataset. It shows the spread of the data by calculating the average distance of values to the dataset’s mean, regardless of whether they are above or below it. In other words, a low standard deviation indicates that most of the values are close to the mean or average of the dataset, while a high value indicates that the values are spread out over a wider range. The formula used to calculate it is straightforward, but if you use a spreadsheet program like Microsoft Excel or Google Sheets, there are many built-in functions that will calculate it for you.
In this guide, you will learn how to calculate standard deviation and review all the built-in functions available to calculate it for you in Google Sheets. You will learn the differences between the multiple versions of the STDEV function, like STDEVP, STDEVA, STDEVPA, DSTDEV, and DSTDEVP. You have step-by-step instructions on how to use the functions with different types of data and in combination with other functions.
How to Find Standard Deviation in Google Sheets?
To calculate the standard deviation of a dataset, you need to sum the square of the differences between each value and the mean, then divide by the size of the dataset (population) or the size of the dataset minus one (sample). Finally, take the square of the result.
Standard Deviation of Population
The formula used to calculate the standard deviation for a population is as follows:
σ = √Σ( xi - μ)^2/N
- σ = standard deviation of population
- Σ = sum
- xi = each value in dataset
- μ = population mean
- N = size of population
Standard Deviation of Sample
To calculate the standard deviation for a sample of the population, use the following formula:
s = √Σ( xi - x̄)^2/(n-1)
- s = standard deviation of population
- Σ = sum
- xi = each value in sample
- x̄ = sample mean
- n = size of sample
The choice between the two depends on the type of dataset you’re working with. If you have access to the data for the entire population, which isn’t often the case, you should use the population version of the formula. If you have a sample of the population, you should use the sample version. In addition to providing both of the formulas described above as built-in functions - STDEV for samples and STDEVP for populations - Google Sheets features some additional versions, all with the same syntax and parameters.
For example, both formulas are also available as database functions: DSTDEV and DSTDEVP. Like DSUM, these functions work with data structured as database-like tables.
Finally, there is another version of these formulas to be used on datasets that may contain text values in some cells, such as a dash or other characters to represent zero. The STDEVA and STDEVPA functions assume that cells with text are equal to a value of 0.
How to Calculate Standard Deviation in Google Sheets with the STDEV Function?
To calculate the standard deviation of a sample, you can use the STDEV function: STDEV(value/array1, value/array2*). Values can be typed or in the form of cell references. The function can be used on single values or arrays. When using single values, there must be a minimum of two.
1. Add Function
Type the STDEV function in an empty cell.
2. Select Dataset
Type the values in your dataset, separated by commas. Alternatively, select the cells containing the values.
3. Get Standard Deviation
Close the parenthesis and press ‘Enter’.
Examples: Other Standard Deviation Functions in Google Sheets
Below, you have examples using STDEV vs. STDEVP, STDEVA vs. STDEVPA, and DSTDEV vs. DSTDEVP to find the standard deviation for different datasets.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MORESTDEV vs. STDEVP
In the example below, I will calculate the population and sample standard deviation for the same dataset to illustrate the difference. The dataset contains changes in price over a year for two vendors selling four different products. I will calculate the standard deviation for each product and vendor.
- 1. In an empty cell, type the STDEV function and select the first set of values.
- 2. Press ‘Enter’ to see the result. Grab the fill handle and drag it right to the last column.
- 3. In the row beneath, I have repeated steps 1 and 2 with the STDEVP function to see the difference.
STDEVA vs. STDEVPA
Below, I will calculate the standard deviations for three tests. Since some of the cells contain dashes to indicate that the student missed the test, I will use STDEVA and STDEVPA so they are converted to zeroes.
- 1. In an empty cell, type the STDEVA function and select the first set of values.
- 2. Press ‘Enter’ to see the result. Grab the fill handle and drag it right to the last column.
- 3. In the row beneath, I have repeated steps 1 and 2 with the STDEVPA function to show the difference.
If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet
READ MOREDSTDEV vs. DSTDEVP
For this last example, I will use the database functions DSTDEV and DSTDEVP. To use these functions, you need to structure your data in tabular form. The first row should contain the headers of field labels, and subsequent rows should contain individual records. With these functions, cells containing text characters will be ignored, not replaced by zeroes.
- 1. Set up the criteria array for the function as shown below. In this case, I want to include all IDs, so I will leave the second row empty in the criteria array.
- 2. In an empty cell, type the DSTDEV function and select the table, including the headers.
- 3. Add a comma followed by the header or field label for the column you want. In this case, the ‘Test1’ column.
- 4. Finally, select the cells containing the criteria array and close the parenthesis.
- 5. Press ‘Enter’ to see the result. Below, I have repeated steps 2 to 5 with the DSTDEVP function to see the difference.
What is the Difference Between STDEV and STDEVP in Google Sheets?
The STDEV function calculates the standard deviation for datasets that constitute a sample of the population. STDEVP, however, calculates it for the population. The difference is in what is used in the formula’s denominator: n is used for a population, and n-1 for a sample.
Conclusion
Calculating standard deviation in Google Sheets is very easy. Higher standard deviation values indicate that the data is spread out, while a lower standard deviation indicates that the values are centered around the mean or average of the dataset. Remember that the value obtained for the standard deviation will vary depending on whether you use the sample or population formula. The standard deviation for a sample will be higher, as it is calculated over n-1, not N.
You now know the formulas used to find the standard deviation for a sample and a population, but you also know that Google Sheets provides multiple versions of the formulas as built-in functions. You have step-by-step instructions on how to find the sample and population standard deviations using STDEV and STDEVP. If some of the cells in your dataset contain text characters that represent zeroes, you can use STDEVA and STDEVPA to convert text values to zeroes. Finally, if your data is structured in a database-like table, you know how to use DSTDEV and DSTDEVP to find the standard deviation for a sample and a population.