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.
What Is Conditional Standard Deviation?
First, we need to know about the 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.
How to Calculate Standard Deviation with IF Conditions in Excel
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 the 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 Calculate Standard Deviation of y Intercept in Excel
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:
- 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 Calculate Population Standard Deviation in Excel
Things to Remember
- For using the 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.
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.
Conclusion
This is how we calculate the 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.
Related Articles
- How to Calculate Standard Deviation of a Frequency Distribution in Excel
- Calculate Percentile from Mean and Standard Deviation in Excel
- How to Calculate Uncertainty in Excel
- How to Calculate Mean and Standard Deviation in Excel
<< Go Back to Standard Deviation Formula in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
this methood is worng since it take all the fulse as 0
and + need to use CTRL+SHIFT+ENTER
Dear AVI,
We can assure you that if you followed the steps correctly, everything should be fine with the tutorial. Moreover, both of these formulas are single-cell output formula. So, the procedure for the array formula implementation or pressing CTRL+SHIFT+ENTER is not required here.
If you want to take all the data to find out the standard deviation without conditions or avoid taking the false as 0, you should have a look at our article How to Calculate Average and Standard Deviation in Excel.
Regards,
Exceldemy