STDEV Function in Excel (Table of Contents)
STDEV Function in Excel
Statistics and spreadsheets are closely related. It is no wonder then that a popular spreadsheet tool like excel would be full of features and functions that support statistical analysis. One of them is the STDEV or corrected standard deviation function.
STDEV Formula in Excel
Below is the STDEV Formula in Excel :
Examples of STDEV function in excel
1) Let us take a look at some examples of STDEV. STDEV is widely used to measure how much the individual data elements are dispersed from the average value.
- For example, let’s say we have a dataset of parts for a store which is being checked for quality. We need to find the standard deviation between the number of parts we have, the number of parts which are to be reworked, parts failed, the parts yet to be checked and the total number of parts.
- The function STDEV would be used like this:
- This would give us the standard deviation for values in the cells A2 till F2. Similarly, we can find out the standard deviation for others.
The analysis of the above data means that the difference between the numbers of parts for each category is high. If the formula returned a lower number, it would mean that the number of parts of each category is closer. This is an important factor in many applications such as analyzing the effectiveness of quality control measures.
2) Let’s look at another example where STDEV function is useful.
Suppose we have weight data for a number of shipments. We want to find out how much they differ from each other.
The data is as below:
Shipment 1: 0.5kg
Shipment 2: 2 kg
Shipment 3: 0.8 kg
Shipment 4: 2.8 kg
Shipment 5: 1.1 kg
- We put this in an excel sheet.
- Now Use the STDEV function to get the standard deviation:
- This would give us the standard deviation in the cell B7.
Now we find that the standard deviation is about 0.9, which means that the weights do not vary much between the shipments. In real life, such problems contain many more data points and use more measures such as average and variance to create solutions to specific problems.
Explanation of the STDEV function
Before we move on to learning about how to use STDEV, here is an overview of the statistical concepts which are used in this function. A key measure of central tendency and dispersion in statistics is Standard Deviation. Standard deviation is calculated by the following formula:
In this formula, one of the values (say 1, etc.) is the mean or average of the numbers, while n is the total sample size. Standard deviation measures how widely each value in a sample vary from the average value. It is basically the square root of the variance. Variance is the average of the squared differences from the mean or average of a dataset.
How to Use the STDEV Function in Excel
Now that we have an overview of the statistics underlying the STDEV function in excel, and few examples of STDEV functions’ uses, let us look at how to use the function and the correct syntax.
- First of all, we need one or more data points to choose from. Once we have done that, we need to pick a cell where we want the result to be displayed
- Now type the function and arguments in the correct syntax and press F2 and Enter.
- And the Result will be :
The syntax of the STDEV function is as follows:
In STDEV, the number1 is called a mandatory argument and corresponds to a sample of the data. The number2 and other numbers are optional arguments but should correspond to a sample of the dataset to get correct results.
STDEV has a number of related functions for use with different types of datasets, the most commonly used are the following:
STDEV.S for a sample of numeric data, it does not calculate standard deviation based on text and logical values.
STDEV.P for calculating the standard deviation for an entire population.
STDEVA for calculations based on logical values and text
We can also use STDEV function from the formula bar as shown below:
- Pick a dataset and select any cell which we would like to display the result in.
- Go to Formulas and click on Insert Function.
- After clicking on the Insert Function a pop-up window appears and then select Statistical in the dropdown
- And after selecting Statistical select STDEV, or type STDEV in the search box and click on go:
- Then select the data that needs analysis.
- After selecting the data click on OK for the result:
- And the Result will be :
Things to Remember About the STDEV Function in Excel
We have seen a few examples of the STDEV function and have also seen how to use it in problems pertaining to data analysis in the examples, here are a few things to remember:
- When using STDEV, the function assumes that the sample is the entire size of the population. So when we want to find out standard deviation from a subset of the whole data, we should use STDEV.S
- The standard deviation formula used in STDEV is called (n-1) method.
- The arguments in STDEV can be numbers or text, arrays of different types and references containing numbers.
- Logical values and text typed directly in the arguments are used in the calculation. If the logical values or text are referenced from an array or are empty cells or error values in the reference, then they are ignored.
- To use logical and text values referenced from elsewhere and not typed directly in the argument, we should use STDEVA function.
- Population in statistics means the entire dataset we are using. A sample is a part of that dataset which we pick to analyze.
Conclusion – STDEV Function in Excel
Data science is one of the most important areas of study currently. Excel is a tool we can use in many applications of data science without resorting to high-level programming or having an in-depth knowledge of the mathematics involved. STDEV and many other such functions make it that much easier for us to be data analysts. However, we need to know the basics and should think about creative solutions to apply them in our work.
Recommended Articles
This has been a guide to STDEV Function in Excel. Here we discuss the STDEV Formula in Excel and How to use the STDEV function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
