Calculating Standard Deviation in Excel

In this article, you will learn what the STDEV, STDEV.S, and STDEV.P functions are and how to use them in Excel.

‍

What are the STDEV, STDEV.S, and STDEV.P functions in Excel?

The STDEV, STDEV.S, and STDEV.P functions are all statistical functions that allow you to calculate the standard deviation, which is a measure of how much variance there is in a set of values in relation to the mean (average). It should be noted that the STDEV function has been replaced by the STDEV.S function however is still compatible with some versions of Excel. With that said:

• The STDEV function makes the assumption that the data input as an argument is a sample of the population and calculates the standard deviation based on that.
• The STDEV.S function in Excel calculates standard deviation using the "n-1" method. It assumes that its arguments are a sample of the population. 
• The STDEV.P function in Excel calculates standard deviation using the "n" method. It assumes that its arguments represent the entire population. 

‍

When are the Excel STDEV, STDEV.S, and STDEV.P functions useful?

The STDEV, STDEV.S, and STDEV.P functions in Excel are useful in various statistical analyses, particularly when you need to understand the dispersion or variability within a dataset. A few examples include:

• Investment Risk Analysis: In finance, standard deviation is a common way to measure investment risk. A high standard deviation means the price of an asset is moving a lot, implying higher volatility and risk. Conversely, a lower standard deviation implies lower risk.
• Quality Control: In manufacturing processes, the standard deviation can be used to measure the amount of variation or dispersion of a set of values. A low standard deviation indicates that the data points tend to be close to the mean, which is ideal in manufacturing as it means consistent product quality.

When it comes to choosing which standard deviation function to use, if your data represents the entire population, then compute the standard deviation using STDEV.P but if your data is a sample of the population, then compute the standard deviation using STDEV.S or STDEV.

‍

How to use the STDEV, STDEV.S, and STDEV.P functions in Excel

Since the STDEV, STDEV.S, and STDEV.P functions have the same arguments, you can easily use all of them once you learn one.

The syntax for the STDEV function is the following:

=STDEV(number1, [number2], ...)

The syntax for the STDEV.S function is the following:

=STDEV.S(number1, [number2], ...)

The syntax for the STDEV.P function is the following:

=STDEV.P(number1, [number2], ...)

number1, number2... are the 1 to 255 arguments for which you want to calculate the standard deviation. You can also reference an array instead of arguments separated by commas (e.g. A1:A10).

‍

For instance, suppose you are an investment analyst and have a dataset of returns from 3 different investments over 10 years and want to know the standard deviation to compare the volatility of the investments. Below is a screenshot of this example.

‍

The STDEV and STDEV.S functions calculate the standard deviation by assuming that the 10-year period is just a sample of the time spent investing, whereas the STDEV.P function calculates assuming that the 10 years are the whole “population” of investing years.

‍

Note: It can be useful to use the STDEV functions in conjunction with the AVERAGE function. The AVERAGE function calculates the mean of the data, and when used together with the standard deviation, these functions can provide valuable insight.

‍

Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.

‍