Multiple If Statements in Excel (Nested IFs, AND/OR) with ExamplesStep by Step Instructions with Screenshots
IF Statements are used in Excel when testing more than one condition and are widely used by many programming languages and by Excel users who deal with massive data daily.
This article will explore the many ways that you can apply the IF Statement and it can be done through the Logical Test and Nested Ifs.
Multiple Ifs Statement
Let us take the data below as a point of example:
Based on the data above, we will be testing the different If statements to test a variety of conditions.
Excel If Statement
This statement is a basic condition test that will only yield two results: True or False.
Based on the example given above, we can determine whether the student will pass or fail.
The formula to be applied is shown below:
=IF(test,[true],[false])
With the example above, let’s say that a student who gets a score below 65 is considered to have failed.
The formula applied is:
=IF(B2>=65,"Pass","Fail").
Nested If
A Nested If Statement refers to a combination of formulas – a formula that is nested inside another formula.
Doing this makes one formula determine the outcome of the other formula inside it.
A simple Nested If Statement refers to a formula having only 1 other formula nested in another formula.
Still using the example given above, we will use two formulas – one to determine whether a student has passed or failed, and another to check whether a cell has been filled with data or is empty.
The formula to be used is:
=If(B2=””,”Incomplete”,If(B2>=60,”Pass”,”Fail”).
Below will be the result of the formula used:
Excel If Statement with Multiple Conditions Range
Assuming that you have multiple conditions to consider in determining the outcome of each cell, this can be done through a formula.
Assuming that additional data have been supplied based on the example above:
With the conditions presented above, the formula to be used to get the outcome in Column C will then be:
=IF(B3<51,”F”,IF(B3<66,”D”,IF(B3<81,”C”,IF(B3,”B”,”A”))))
The outcome of the formula based on the above is shown below:
Excel If Statement with Logical Test (And / Or)
With various conditions that need to be tested, it is more effective to use the AND / OR function.
Excel If AND Function
This function is especially useful should you wish the conditions presented to return as True.
Take the example shown below:
The formula to put in Column D under commission will be:
=IF(AND(B2>=5000,C2>=5000),10%,IF(AND(B2>=4001,C2>=4001),5%,IF(AND(B2>=3001,C2>=3001),4%,IF(AND(B2>=2001,C2>=2001),3%,IF(AND(B2>=1501,C2>=1501),2%,IF(AND(B2>=1,C2>=1),1%,""))))))
The above will show the commission results below:
Excel If OR Function
With multiple conditions that need to be checked using a logical test, using the IF function will return the outcome to be TRUE if any of the outcomes validated is TRUE.
Still using the above data, we will replace it with the OR function so the formula will be:
=IF(OR(B2=5000,C2>=5000),10%,IF(OR(B2>=4001,C2>=4001),5%,IF(OR(B2>=3001,C2>=3001),4%,IF(OR(B2>=2001,C2>=2001),3%,IF(OR(B2>=1501,C2>=1501),2%,IF(OR(B2>=1,C2>=1),1%,""))))))
The formula above will result in a different commission percentage as shown below:
