Standard Deviation in Excel

Standard Deviation in Excel (Table of Contents)

• Standard Deviation in Excel
• Standard Deviation Formula in Excel
• How to Use Standard Deviation in Excel?

Standard Deviation in Excel

Standard Deviation shows how the entire population from the selected area differs from the mean point of the selected values. This helps in finding out and categorizing the values from the mean. We can calculate the Standard deviation in excel with three functions which are STD, STD.P, and STD.S where STD is not available in the latest version of excel, STD.P is used when we want to consider the entire population and STD.S is used when we want to consider the sample data only.

Sample (STDEV.S) Standard Deviation in Excel

• Standard Deviation function can be used as a worksheet function & can also be applied by using VBA code.
• It is most commonly used by investors to measure the risk of a stock (a measure of stock volatility over a period of time). Financial analyst often uses it for measuring and managing risk for a specific portfolio or fund.
• It is also used in election polls and surveys results (i.e. who is going to win an election) & weather prediction.

Definition

Standard deviation is a calculation that determines how much your values or datasets deviate (spread out) from the AVERAGE or MEAN value.

This Excel shows whether your data is near or close to the average (mean) value or not.

Three possible scenarios with Standard deviation equation is

1. If there is a higher the standard deviation, then there is more variation in the data and It indicates mean or average value is less accurate.
2. If the standard deviation is equal to 0, then it indicates that every value in the dataset is exactly equal to the mean or average value.
3. If the standard deviation is close to zero, then there is lower data variability and the mean or average value is more reliable.

Standard Deviation Formula in Excel

Below is the Standard Deviation Formula in Excel :

The Standard deviation formula in excel has below-mentioned arguments:

• number1: (Compulsory or mandatory argument) It is the first element of the sample of a population.
• [number2]: (Optional argument) It is a number of arguments from 2 to 254 corresponding to a sample of a population.

Note: If you have already covered the entire sample data through the range in number1 argument, then no need to enter this argument.

Note: The function in excel ignores logical values and text data in the sample.

Excel STDEV function can accept up to 255 arguments. Where it can be represented by either, named ranges or numbers or arrays or references to cells containing numbers.

In Excel 2016, if we type =std or =dstd, 8 types of Standard Deviation Formulas appear

Here, the 8 types of Standard Deviation are categorized under two groups

a) S, STDEVA, STDEV, DSTDEV will come under Sample.

Whereas, b) STDEV.P, STDEVP, STDEVPA, DSTDEVP will come under Population.

The main difference between sample and population is

Population (STDEV.P): Where “P” stands for “Population”, It includes all the elements from a data set in Population (N).

Sample (STDEV.S): Where “S” stands for “Sample”, Only the sample of the data set is considered from an entire data set (N-1).

Note: Here Sample means only a few elements are taken out from a large population.

The selection of standard deviation formula for a particular task is based on the logical or text values present in the datasets. The below-mentioned table will help you out.

How to Use Standard Deviation in Excel?

It is very simple and easy to use. Let us understand the working of Standard Deviation in Excel by some Standard Deviation Formula example.

Example #1 – Calculation of Standard Deviation for Height Data

In the below-mentioned table, it contains three columns, Serial number in column B (B8 to B20), Name in column C (C8 to C20) & Height of person in column D (D8 to D20).

I need to find out the Standard deviation of Height of a person.

Prior to the calculation of Standard deviation in excel, we need to calculate sum & mean (Average) value for the datasets.

Where the sum value is calculated with the help of the sum formula i.e. =SUM (D8:D20) in cell G10.

Result is :

And Mean (Average) is calculated with the help of Average formula i.e. =AVERAGE (D8:D20) in cell G11.

Result is :

Let’s apply Standard deviation function in cell “G14”. Select the cell “G14” where Standard deviation function needs to be applied.

Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “Standard deviation” in the search for a function box, 6 types of Standard Deviation Formulas will appear in select a function box.

Here we are calculating Standard deviation only for the sample of the data set, which is taken out from a large population, therefore we need to select either of them i.e. STDEV.S or STDEV. Let’s STDEV.S (for a sample) from the Statistical category. Double click on STDEV.S in excel.

A dialog box appears where arguments for Standard deviation function needs to be filled or entered i.e. =STDEV.S (number1, [number2], …)

=STDEV.S(D8:D20) Here the Height data is present in the range D8:D20.

To enter Number 1 argument, click inside cell D8 and you’ll see the cell selected, then Select the cells till D20. So that column range will get selected i.e. D8:D20.

[number2] Here we have already covered the entire sample data through a range in number1 argument, therefore no need to enter this argument. Click ok, after entering Standard deviation arguments.

=STDEV.S(D8:D20) i.e. returns the Standard deviation value 1.12 as the result.

Standard deviation value of 1.12 indicates that most of the people in the group would be within the height range of 174.61 (with the standard deviation of +1.12 or -1.12)

Here, the standard deviation is close to zero, therefore it indicates lower data variability and the mean or average value is more reliable.

Note: When we apply the formula to larger datasets, we will see the bigger difference.

Things to Remember about Standard Deviation in Excel

• Number Arguments must contain at least two or more numeric values to calculate Standard Deviation in excel.
• Most of the cases, we use S formula to calculate standard deviation in excel because we only consider the sample of the data set from an entire data set (N-1).
• #DIV/0! error Occurs if less than two numeric values in the number argument of Standard Deviation (S) function.

• #VALUE error occurs if any of the given values in number argument are text values in S & STDEV.P. (Note: If Standard Deviation function is not able to interpret text value as numeric values, then #VALUE error)
• S & STDEV.P function can be applied to multiple ranges or groups.

Recommended Articles

This has been a guide to Standard Deviation in Excel. Here we discuss the Standard Deviation Formula in excel and how to use the Standard Deviation in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. SUM Function in Excel
2. Excel AVERAGE Function
3. SUMIF in Excel
4. Excel LARGE Function