LiveFlow raises $13.5 million Series A led by New York-based Valar Ventures and launches LiveFlow Next to revolutionize finance!
Read More
Arrow
Back to guides
Excel Formulas

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.

Examples of using STDEV, STDEV.S, and STDEV.P in Excel

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.

Learn how to do this step-by-step in the video below 👇

Automate financial reporting with LiveFlow

Cta Photo

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how!

Get personal help

We guarantee you personal help on chat or Zoom within maximum 6 hours between 9am and 10pm EST.
Blue Tick
Email us at: help@liveflow.io

Liked this article? Then you'll love the ones below

Supercharge your financial reporting today

See LiveFlow in action and discover how to streamline your workflows.

Book a demo