How to Calculate Standard Deviation with IF Conditions in Excel

Method 1 – Calculate Standard Deviation with Single IF Condition

Steps:

  • Select the desired cell where we want to get the result as shown in the image below.

selecting the cell to put Standard Deviation function with Single IF Condition

  • Enter the following formula below.
=STDEV(IF(D5:D16="Oil",E5:E16))

Standard Deviation IF with Single Criteria in Excel

Formula Breakdown

  • The IF(D5:D16=”Oil”,E5:E16) returns only the values of Revenue that are based on criteria D5:D16=”Oil”. We will get only the revenues from the Oil
  • 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.
  • The STDEV will find out the standard deviation of the selected data set by the IF function. We will get the standard deviation of revenue only from the oil facility.
  • Press Enter to get the result.

output from Standard Deviation with Single IF Condition

Read More: How to Calculate Standard Deviation of y Intercept in Excel


Method 2 – Calculate Standard Deviation with Multiple IF Conditions

Steps:

  • Select the destination cell.

selecting the cell

  • Enter the following formula below.

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

Standard Deviation IF with multiple Criteria in Excel

Formula Breakdown

  • The =STDEV() function is to find out the standard deviation.
  • The IF function is to select the data range based on criteria.
  • We needed the data of month January and facility Oil. So, by entering (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.
  • We entered the range values we need to select from, that being E5:E16.
  • Combine them to get a standard deviation based on multiple criteria.

  • Press Enter to get the expected result.

Showing results

Read More: How to Calculate Population Standard Deviation in Excel


Download Practice Workbook


Related Articles


<< Go Back to Standard Deviation Formula in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nasir Muhammad Munim
Nasir Muhammad Munim

Nasir Muhammad Munim has been an Excel and VBA Content Developer for over a year in Exceldemy and published more than 30 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Electrical and Electronic Engineering from the Islamic University of Technology. Apart from creating Excel tutorials, he is interested in developing PostgreSQL, MySQL, and Android applications. He is fascinated by CAD-based designing systems and building... Read Full Bio

2 Comments
  1. this methood is worng since it take all the fulse as 0
    and + need to use CTRL+SHIFT+ENTER

    • Reply Nasir Muhammad Munim
      Nasir Muhammad Munim Aug 28, 2023 at 1:35 PM

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo