Standard deviation is a commonly used statistic when analyzing data sets.
A standard deviation value would tell you how much the dataset deviates from the mean of the data set.
For example, suppose you have a group of 50 people, and you are recording their weight (in kgs). In this dataset, the average weight is 60 kg, and the standard deviation is 4 kg. It means that most of the people’s weight is within 4 kg of the average weight (which would be 56-64 kg).
Interpreting standard deviation value:
- A lower value indicates that the data points tend to be closer to the average (mean) value.
- A higher value indicates that there is widespread variation in the data points. This can also be a case when there are many outliers in the dataset.
Calculating Standard Deviation in Excel
While it’s easy to calculate standard deviation in Excel, you need to know which formula to use in Excel. There are six standard deviation formulas in Excel (eight if you consider database function too).
These six formulas can be divided into two groups:
- Calculating the standard deviation for a sample: The formulas in this category are STDEV.S, STDEVA, and STDEV
- Calculating the standard deviation for a population: The formulas in this category are STDEV.P, STDEVPA, and STDEVP
In almost all of the cases, you will use standard deviation for a sample.
Again in layman terms, you use ‘population’ when you want to consider all the dataset in the population. On the other hand, you use ‘sample’ when using population is not possible or unrealistic and you pick a sample from the population.
You can use the sample data to calculate the standard deviation and infer for the entire population. You can read a great explanation of it here (read the first response).
So this narrows down the number of formulas to three (STDEV.S, STDEVA, and STDEV)
Now let’s understand these three formulas:
- STDEV.S – Use this when your data is numeric. It ignores the text and logical values.
- STDEVA – Use this when you want to include text and logical values in the calculation (along with numbers). Text and FALSE is taken as 0 and TRUE is taken as 1.
- STDEV – STDEV.S was introduced in Excel 2010. Before it, STDEV was used. It is still included for compatibility with prior versions.
So you can safely assume that in most of the cases, you would have to use STDEV.S function (or STDEV if you’re using 2007 or prior versions).
So now let’s see how to use it in Excel.
Using STDEV.S Function in Excel
As mentioned, STDEV.S function uses the numerical values but ignores the text and logical values.
Here is the syntax of STDEV.S function:
- Number1 – This is a mandatory argument. The first number argument corresponds to the first element of the sample of a population. You can also use a named range, single array, or a reference to an array instead of arguments separated by commas.
- Number2, … [Optional arguement] You can use upto 254 additional arguments. These can refer to a data point, a named range, a single array, or a reference to an array.
Now let’s have a look at a simple example of calculating standard deviation in Excel.
Example – Calculating the Standard Deviation for Weight Data
Suppose you have a dataset as shown below:
To calculate the standard deviation using this data, use the following formula:
The value of 2.81 indicates that most of the people in the group would be within the range 69.2-2.81 and 69.2+2.81.
Note that when I say ‘most of the people’, it refers to the normal distribution (that is 68% of the data points are within one standard deviation from the mean).
Also, note that this is a very small sample set. In reality, you may have to do this for a bigger dataset where you can observe normal distribution better.
You May Also Like the Following Excel Tutorials:
- Calculating Weighted Average in Excel.
- Calculating CAGR in Excel.
- Calculate and Format Percentages in Excel.
- Calculate Age in Excel using Formulas.
- Creating a Bell Curve in Excel.
- Calculating Compound Interest in Excel.