In statistics, standard deviation is a measure of how scattered a dataset is in relation to its mean. In simple terms, it tells you how “distributed” a set of data points is.
This is useful for things like understanding how different student grades are in a class, or measuring how widely the temperature of something fluctuates over time. This can especially help you understand the differences between two datasets that may have the same mean.
Like two classrooms of students who have the same baseline overall average grade, but with multiple students who can be much worse (or much better) in one class and not in the other.
This is mathematically calculated by extracting the square root of the variance of the dataset. This article will show you how to calculate standard deviation in Excel.
Typical uses of standard deviation
There are many ways to manipulate data in Excel, and standard deviation functions are just another powerful tool available to you.
When do people usually use the standard deviation calculation? This is actually quite common for data analysis across many industries.
– /
Here are some examples:
- Population studies: health researchers may be interested not only in determining the difference in metabolic rates between men and women, but also in how much these rates differ between the two groups.
- Scientific evidence: Measurements from experiments with results that differ less from the average usually indicate stronger evidence than measurements that differ greatly.
- Industrial quality: Measuring how much size or quality of a product comes off the production line varies, may indicate how well that machine is producing the product within acceptable specifications.
- Financial Risk: Stock analysts use standard deviation to measure how much the value of stocks or other assets varies, which can indicate whether an investment is risky or not.
How to calculate standard deviation in Excel
Regardless of why you might want to calculate the standard deviation of a dataset, Excel makes it very easy.
Excel has two forms of standard deviation.
- Sample standard deviation: Using one dataset from a sample of a larger population.
- Population standard deviation: Uses all datasets from the entire population.
- STDEV.S: standard deviation of a numeric dataset
- STDEVA: standard deviation of a dataset, including text characters such as “False” â€Or 0
- STDEV: Same as STDEV. S, but is used in tables created in Excel 2007 or earlier.
- TRUE evaluates to 1
- FALSE evaluates to 0
- Any other text evaluates to 0
In most cases, it is not possible to use data for the entire population (for example, to measure metabolic rate in women), so it is much more common to use the sample standard deviation and then display the results for the entire population.
There are six standard deviation formulas available in Excel:
The STDEV.P, STDEVPA, and STDEVP functions work the same as the function above, but use datasets from the entire population, not a sample.
How to use the STDEV.S and STDEV.P function
Using standard deviation functions in Excel is quite simple. You just need to provide the entire dataset to the function.
In the following example, we will take a government SAT score dataset for New York schools and determine the standard deviation of math scores.
Since the dataset containing the math scores is in the range D2 to D461, just select any cell where the standard deviation should be and type:
= STDEV.P (D2: D461)
Press Enter to complete the formula entry. You will see that the standard deviation for the entire dataset is 64.90674.
Now imagine that you don’t have a complete dataset for all schools in the state, but you still want to take the standard deviation of a sample of 100 schools, which you can use to draw conclusions about all schools.
It won’t be so accurate, but should still give you an idea of ??the truth.
Since the dataset containing the math scores is in the range D2 to D102, just select any cell where the standard deviation should be and type:
= STDEV.S (D2: D102)
Press Enter to complete the formula entry. You will see that the standard deviation for this smaller sample of data is 74.98135.
This is a good example of how much more accurate an image can be obtained with a much larger sample size. For example, the same STDEV.S formula used for a sample of 200 schools returns 68.51656, which is even closer to the real standard deviation for the entire data set.
How to use the STDEVA Excel function
The standard deviation function STDEVA is rarely used because most datasets used by humans are populated with only numerical data. But there may be situations when there are text values ??inside the data.
This is how STDEVA handles text data.
One example where this can be useful is a sensor on a machine that measures fluid temperatures above 0 degrees Celsius.
You can program the sensor so that when the temperature sensor is disconnected, it writes FALSE to the data stream. When you do a standard deviation calculation in Excel, these “FALSE” data readings will be converted to 0 in the dataset before the standard deviation is calculated.
Formula:
= STDEV (C2: C100)
Press Enter when finished. The result in this case was 4.492659. This means that the entire sample dataset of just under 100 points deviated from the overall mean by just under 5 degrees.
This result takes into account that the data reading FALSE is 0 degrees.
As with the STDEV.S function, if you have an entire dataset containing text records, you can use the STEVPA function to calculate the standard deviation for that population.
Remember, if you are using an older version of Excel that does not have any other standard deviation functions available, you can still use STDEV and STDEVP, which work the same way for calculating the standard deviation in Excel, as in the examples above. However, these functions cannot use text or logical data.
Be sure to check out our other helpful tips and tricks for using Excel And share your own apps of standard deviation functions in the comment section below.
–