What is Standard Deviation in Excel?
Standard Deviation in excel was an inbuilt function in excel for 2007 and older versions which was used to obtain the standard deviation based on a sample given as argument, but in excel versions 2010 and above we have other formulas to calculate standard deviation which is STDEV.P and STDEV.S.
For Example: Suppose you have data points 5, 3, 6, 8 and 10.
- Total Data Points: 5
- Sum of Data Points: 32
- Average (Mean) = 32/5 = 6.4
- Standard Deviation excel = 2.7
This means that most data points range is within 2.7 of the average value i.e. between 3.7 to 9.1 (either side of the average value of 6.4).
- If the Standard Deviation value is lower, then the frequency of the data points is closer to the mean (average) value.
- If the Standard Deviation value is higher, then the frequency of the data points is wider to the mean (average) value.
Calculate Standard Deviation in Excel
When we deal with quantitative data we always to look for a typical element of the data set. What is the middle of the data point? i.e. the average value or mean of the data points.
Standard Deviation helps us to understand how spread out a data is. Especially in the finance industry, price data is used as a measure of volatility.
Below examples will allow us to understand the concept of Standard Deviation Excel practically.
Below are the scores of the skill level of the employees in a company. From this data set, we need to calculate the Standard Deviation value.
Follow the below steps to calculate the Standard Deviation in Excel.
Step 1: Calculate the Mean (average) of the data in excel.
Mean = 55.2
So the average value of the data is 55.2 i.e. average score of the skill level of the employees is 55.2
Step 2: Calculate each employees score difference from the mean value and find the difference.
Variance = 3.36
Step 3: Calculate SD (Standard Deviation Excel)
SD is just the square root of the Variance.
SD = 1.83
Conclusion: So the story of this calculation is the score range of the employees is ranging from 53.37 to 57.03.
Standard Deviation Formulas in Excel
In excel we have totally 8 types of Standard Deviation Formulas in Excel.
These 8 formulas are under two groups Sample & Population.
STDEV.S, STDEVA, STDEV, DSTDEV is under Sample.
STDEV.P, STDEVP, STDEVPA, DSTDEVP is under Population.
- Population means you are considering the entire data set.
- Sample means it is very difficult to use the entire data and you are taking only the sample of the data set.
We can use sample data of the entire data set to calculate the Standard Deviation and draw inferences for the entire data set.
- In almost all the case, we use the STDEV.S formula to calculate standard deviation in excel. This is used when we want to use only the numerical values and ignore the text values.
- If at all you want to use text values in the range use STDEVA. It takes text and FALSE value as 0 and TRUE as 1.
Using STDEV.S Formula for Standard Deviation in Excel
The formula of STDEV.S in excel includes only numbers.
- Number 1: The first value of the sample of the entire population. You can select the range here.
- Number 2: Optional argument. If you have covered the entire sample data through range then this becomes optional.
How to Use STDEV.S Function in Excel?
Below is the data of the heights of the goat and the height of each goat at the shoulder level is below.
Note: Heights are in millimeters.
Step 1: Calculate the Mean value i.e. average value.
Step 2: Apply STDEV.S in excel formula to the range B2:B6.
So the Standard Deviation of the height of the goats is 165 (nearest to the millimeter)
The value 165 millimeter indicates that most of the goat’s height would be within the range of 229 and 559 millimeters
That is the either side of the mean value i.e. 394 – 165 = 229 & 394 + 165 = 559.
Note: This is the Standard Deviation of most of the goats means only a few are in this height range. When we apply the formula to larger datasets we will see the bigger difference.
Things to Remember
- In STDEV.S in excel, “S” represents sample data set.
- It will ignore text values.
- STDEVA considers both text and numerical values. TRUE= 1 and FASLE = 0.
- Sample means only a few elements of the large population.
- There must at least two numerical values.
- S is available from 2010 and later versions. In earlier versions, STDEV is the formula.
This has been a guide to Standard Deviation in Excel. Here we discuss the STDEV.S Formula to calculate Standard Deviation in excel along with examples and downloadable excel templates. You may also look at these useful functions in excel –