Using Excel If Statements with Multiple Conditions Range (And/Or)An essential part of Excel Data Analysis

Adiste Mae

The purpose of using complicated and extensive Excel If statements with multiple conditions range are essential when it comes to data analysis that needs to test certain conditions within a specific range at a single point in time.

A logical test might also be needed to test the different conditions. 

The If statements are actually the most used formula for those who work with data analysis daily and are familiar with this function. 

Excel If Statements Multiple Conditions Range

Here are the different If Statements that you can use, depending on the condition that you need it for:

Excel If Statement

Nested If Statement

Suppose we have the below conditions:

  • If the score is between 0 to 50, the grade is F
  • If the score is between 51 to 60, the grade is D
  • If the score is between 61 to 75, the grade is C
  • If the score is between 76 to 90, the grade is B
  • If the score is between 91 to 100, the grade is A

The syntax of the formula based on the above condition then becomes:

=IF(B2<50, “F”,IF(B2<61, “D”, IF(B2<76,”C”,IF(B2<91,”B”,”A”))))

Excel If With Logical Test

When using Excel If Statements, there are two different conditions that can be used to perform a logical test – AND and OR.

AND Function

Using this function, the outcome that Excel will give when the logical test is run is True in every condition, otherwise, it will be False.

OR Function

The outcome will be True when the logical test is run when any of the situations match, otherwise, the outcome will be False.

When AND and OR Functions are also used, there usually is multiple testing that needs to be done.

Over time, you will be well-versed in using these two functions with the If Statement.

How to Apply IF and AND Functions

  1. First, we need to take the data of each of the student’s scores in Science and English.
  2. The score in Science is stored in Data B, whereas the score in English is stored in Data C.
  3. As an example, let’s say that a student will be able to pass if his or her score is greater than or equal to 50 in Science and greater than 75 in English.
  4. To get the result for all the students within only seconds, the If Statement will be combined with the AND function.
  5. To run the formula, the following can be entered: =IF(Excel will then display the logical hint below the F2 cell. It will show the parameters of this function which are logical_test, value_if_true, value_if_false.
  6. The first parameter specifies the condition that needs to be matched. This logical test also allows a combination of IF and AND functions.
  7. The second parameter will show the value if the condition is true.
  8. Lastly, the third parameter will show the value of the condition is false.
  9. Applying the IF and AND formula, you will get =IF(AND(B2>=75,C2>=75), “Pass”, “Fail”)
  10. Add the pass/fail column. Add the formula in the cell and copy-paste it to the rest of the cells below. You will then see the result for each student.

How to Use IF with OR function in Excel

Using this function is the same as using the IF and AND functions. The only difference is that if the condition is true, it only shows true. 

Assuming the example above, the formula will then be =IF(OR(B2>=50,C2>=75), “Pass”, “Fail”)

How to Use IF with AND & OR Functions

This is applicable when testing multiple conditions based on different data at one point in time.

The formula that makes the most sense is using the IF with AND & OR Functions.

Examples are shown below:

  • 1st Situation: If Column B>=70 & Column C>=85
  • 2nd Situation: If Column B>=80 & Column C>=85

If all of the conditions stated above are met, the student will pass.

Otherwise, the student fails.

The formula to be used is:

=IF(OR(AND(B2>=70,C2>=85), AND(B2>=80,C2>=85)),”Pass”, “Fail”)

Excel IF Statement with Other Functions

Below are examples of using the IF Statement with other functions:

IF Statement with Sum, Average, Min, and Max Functions

Suppose that based on the student’s score you wish to mark them as Good, Satisfactory, or Poor.

If you have a predefined formula such as the one shown below and data modification is not allowed:

=If((B2+C2)>=150, “Good”, If((B2+C2)=>100, “Satisfactory”, “Poor”))

Using the SUM Function

=If(SUM(B2:C2)>=150, “Good”, If(SUM(B2+C2)=>100, “Satisfactory”, “Poor”))

Using the AVERAGE Function

=If(AVERAGE(B2:C2)>=150, “Good”, If(AVERAGE(B2+C2)=>100, “Satisfactory”, “Poor”))

Using MAX / MIN Function

If you wish to find who got the highest score from the pool of students, you can use the MAX function.

In the same way, you can use the MIN function to find out who got the lowest score.

The formula to get the highest score then is:

=If(B2=Max($B$2:$B$10), “Best result”,” “)

The formula to get the lowest score will also be:

=If(B2=Min($B$2:$B$10), “Worst result”,” “)

Combining these two formulas: 

=If(B2=Max($B$2:$B$10), “Best result”, If(B2=Min($B$2:$B$10), “Worst result”,” “))

You can now start using the four different types of Excel If Statements.

Depending on what data analysis you need with the information at hand, you will be able to start applying these functions.