Excel is what people have extensively used for analyzing statistics and data.
Often, people use standard deviation whenever they do statistical calculations.
Our tutorial will show you how you can use simple Excel formulas to calculate the standard deviation.
However, before we get into this, allow us to provide you with a brief overview of standard deviation and how you can use it.
What is Standard Deviation?
Standard deviation provides a value that can tell you that the data set deviates from the data set’s mean.
For example, pretend that there is a group of thirty people, and you are making a record of their weight in kilograms.
In our data set, they have 60 kilograms as their average weight. Four kilograms is their standard deviation.
Because of that, most of them have a weight that is within 4 kg of the average weight.
The average weight is around 56-64 kilograms.
Here is an interpretation of the value in standard deviation:
- Having a higher value indicates a wide variety of data points. That may be the case whenever the data set has a lot of outliers.
- Whenever the data points tend to be close to the mean or average value, the standard deviation has a lower value.
Calculation of the Standard Deviation in Excel
Even though calculating the standard deviation in Excel may be easy, you have to be aware of the formula you need to use in Excel.
In Excel, there are around six standard formulas in deviation that you can use. If you also want to consider the database functions, it can go up to eight.
You can divide these formulas into the following groups:
- If you want to calculate the standard deviation for a whole population, these are formulas that you can use in this category: STDEVPA, STDEVP, and STDEV.P.
- Whenever you want to calculate the sample standard deviation, use the formulas STDEV, STDEVA, and STDEV.S in this category.
In every case, for a sample, what you will use is the standard deviation.
In layman’s terms, you can have the word ‘population’ used whenever you want to have all of the datasets in the whole population considered.
Otherwise, you can have the term ‘sample’ used whenever you use a population that may be unrealistic or impossible to do.
For these cases, you need to get a sample of the population.
Use Sample Data
If you want to calculate the whole population’s infer and standard deviation, you can utilize the sample data.
The sample data is a subset of the population that you can use to draw inferences on the population that you are studying provided with specific conditions.
Because of this, we can measure the mean height of the women.
We can do this by using a population sample that we can refer to as a statistic.
We can then have this used to draw inference on the population’s parameter of interest.
It may involve some inaccuracy and uncertainty in concluding the population with this inference when you base it on a sample.
If your sample has only a few members, you will lose some of the information compared to the whole population.
Choose A Sample to Study
Choosing a sample and a study has so many ways that people refer to as the sampling theory.
The SRS or Simple Random Sampling is the method that is commonly used.
For SRS, all of the population members have an equal probability of being a part of the sample, which is why they use “random” as a term for this.
Other sampling methods like cluster sampling, stratified sampling, and others all come with pros and cons.
It is vital to always keep in mind that the sample we get from the population is only one of the many possible selections.
Whenever ten researchers study the same population, they may get different answers when they draw their samples.
In our previous example, all of the researchers could arrive at different mean heights for women as the questioned statistic (height) can be different from one sample to another.
It comes with a distribution that they refer to as a sampling distribution.
People can have this distribution used whenever they want to know the uncertainty in their estimate of the parameter in the population.
Get the Sample Mean’s Sampling Distribution
The sample mean’s sampling distribution is what people know as a normal distribution with a standard deviation equal to the sample standard deviation, divided by the sample size.
Thus, people can easily get confused with the sample’s standard deviation as the sampling distribution’s standard deviation is what people commonly refer to as the standard error.
Because of that, the number of formulas gets narrowed down to three. These are the STDEV, STEV.S, and STDEVA functions.
Let us now understand these formulas:
- STDEV – In Excel 2010, STDEV.S was introduced. Before that, what people have been using is the STDEV function that is still compatible with the past versions.
- STDEV.S – This is what you use whenever you have numeric data that ignores the logical values and the text.
- STDEVA – You can have this used whenever you want logical values and text included in the calculation with the numbers. TRUE gets taken as 1 while FALSE and Text are taken as 0.
Because of this, it can be safe for you to assume that you can use the STDEV.S function in most cases.
Whenever you use Excel 2007 or previous versions, you can use the STDEV function.
Now, let us check out how you can use this in Excel.
How to Use Microsoft Excel’s STDEV.S Function
Just as we mentioned before, numerical values are what the STDEV.S function uses.
However, it ignores logical values and text.
Here is the STDEV.S function’s syntax:
- Number1 – As the formula’s mandatory argument, the argument’s first number corresponds to the population sample’s first element. You can also have the reference, single array, or name ranged to an array used instead of commas separate arguments.
- Number2 – Here is the formula’s optional argument for using a maximum of 254 additional arguments. You can refer to these as a reference to an array, a single array, a named range, or a data point.
Here is an example where we can calculate the standard deviation:
An Example on How to Calculate for Weight Data’s Standard Deviation
Assume that you have this data set:
If you want to use this data set to calculate the standard deviation, this is the formula that you need to use:
Whenever you use Excel 2007 or any past versions, you may not use the STDEV.S function.
For this case, use the formula below:
This formula will return 2.81 as the value. Indicated here is a significant proportion of the group that could be within the weight range from a minimum of 69.2-2.81 to a maximum of 69.2+2.81.
Take note that when I mention ‘significant proportion of the group,’ it refers to the sample’s average distribution.
Here, 68% of the sample population is within one standard deviation from the mean.
Make sure to take note that this sample set is relatively small.
You may need to do this for a much bigger sample data set that would allow you to be better at observing normal distribution.
Become Aware of How to Calculate Using Standard Deviation in Excel with ExcelMaster
Make yourself an Excel expert with us!
Allow yourself to have knowledge on how to calculate by using Excel’s Standard Deviation with ExcelMaster NOW!