How to Calculate Standard Deviation with IF Conditions in Excel

by admin |Lượt xem: 5

Standard Deviation based on Criteria in Excel

While working on Excel, we often need to use a very common term called Standard Deviation. We can use the standard deviation based on conditions. This leads to the term Standard Deviation If in Excel. In this article, we will learn about how we can calculate Standard Deviation If in Excel.

Table of Contents

Download Practice Workbook

Download the following workbook that we used to write this article so that you can practice along with it while reading the article.

What Is Conditional Standard Deviation?

First, we need to know about Standard Deviation itself. Standard deviation in statistics represents how much a set of data differs from the mean value of them.

Conditional standard deviation means we will find out the deviation of any data set from their mean based on a particular condition. First, we will separate a dataset based on a condition using the IF function. Then we will find out the mean using the STDEV function.

2 Examples of Standard Deviation Calculation in Excel with IF Conditions

When it comes to choosing conditions, we may have 2 choices to start with. We can apply only one criterion to separate the dataset, or we can apply multiple criteria to separate the data, and then we can find out the Standard Deviation. Both of these choices are described with appropriate examples below.

We will use the following dataset for example. Here we see the income from particular facilities for particular months of years for any company.

Example 1: Calculate Standard Deviation with Single IF Condition

Here we will use only one criterion to separate the dataset from the bigger one. For example, here we want to see the standard deviation of the earnings from the Oil facility. It is a good example of calculating standard deviation with if in excel. To do so, we will follow these steps.

🔶 Steps:

First, we will select the desired cell where we want to get the result like the image below.

Second, we will type the following formula in the formula bar as shown in the image below.

=STDEV(IF(D5:D16="Oil",E5:E16))

Formula Breakdown

Firstly, the IF(D5:D16=”Oil”,E5:E16) returns only the values of Revenue that are based on criteria D5:D16=”Oil”. So it means, we will get only the revenues from the Oil
Now here we need to remember that the D column or the Facility column is the criteria for choosing the dataset. The E column or the Revenue column is the data range-making column. You can use your own data range and choose criteria according to your needs.
Secondly, the STDEV will find out the standard deviation of the selected data set by IF function. As a result, we will get the standard deviation of revenue from the oil facility only.

Third, press Enter to get the result.

Read More: How to Use DSTDEV Function in Excel (3 Practical Examples)

Example 2: Calculate Standard Deviation with Multiple IF Conditions

We can separate or select data based on multiple criteria as well by inserting multiple criteria in the IF function. Then we will find out the standard deviation from them.

Suppose we want to find out the standard deviation of Oil facility earnings in the January months only. It is an example of standard deviation if having multiple criteria in excel. The steps are described below.

🔶 Steps:

At first, select the destination cell like before.

Then write the following formula in the formula bar.

=STDEV(IF((C5:C16="January")*(D5:D16="Oil"),E5:E16,""))

Formula Breakdown

In the beginning, we wrote the =STDEV() function to find out the standard deviation.
Then we used the IF function to select the data range based on criteria.
Here, we needed the data of month January and facility Oil. So, by writing (C5:C16=”January”)*(D5:D16=”Oil”), we defined the cell range and multiplied these two criteria as these conditions are dependent on finding the value.
Next, we wrote the range values we need to select from that being E5:E16.
Lastly, by combining them, we will get a standard deviation based on multiple criteria.

Finally, press Enter to get the expected result.

Read More: How to Use Database Functions in Excel (With Examples)

Things to Remember

For using IF function, the desired value cell range is crucial.
Multiple criteria should be multiplied as they are dependent on each other while separating values.
Standard Deviation will always return a numeric value no matter what the data type is.

Conclusion

This is how we calculate standard deviation with if conditions in excel. Hope this article will help you. If you’re still having trouble with any of these examples, let us know in the comments. Our team is ready to answer all of your questions. For any excel-related problems, you can visit our website Exceldemy for solutions.