Nested IF Function in Excel
IF function in excel is simple when there is a single logical test, however, when there are more than one logical tests, then it becomes a little complicated. The logic of testing multiple IF conditions are called Nested IF Function in excel. In today’s article, we will exclusively concentrate on Nested IF Function in excel. This article includes a wide variety of nested IF formulas along with other two important logical functions AND & OR Function.
Examples of NESTED IF Function in Excel
The following examples are used to calculate the Nested IF Function in Excel:
Example #1 – NESTED IF Function in Excel
Now take a look at the popular nested IF example. Based on the students score we need to arrive their standards. Consider the below data for an example.
In order to arrive the results we need to test below conditions and these conditions are nothing but our logical tests.
- If the score is >=585 result should be “Dist”
- If the score is >=500 result should be “First”
- If the score is >=400 result should be “Second”
- If the score is >=350 result should be “Pass”
- If all the above conditions are FALSE then the result should be FAIL.
Ok, we have totally 5 conditions to test. The moment logical tests are more than on we need to use nested IF’s to test multiple criteria’s.
Step 1: Open IF condition and pass the first test i.e. test whether the score is >=585 or not.
Step 2: Now if the above logical test is TRUE then we need the result as “Dist”. So enter the result in double quotes.
Step 3: Now the next argument is if the value or test is FALSE. If the test is false I have 4 more conditions to test, so open one more IF condition in excel in the next argument.
Step 4: Now test the second condition here. The second condition is to test whether the score is >= 500 or not. So pass the argument as >=500.
Step 5: If this test is true the result should be “First”. So enter the result in double quotes.
Step 6: We have already entered two excel IF conditions, if these two tests are FALSE then we need to test the third condition, so open one more IF now and pass the next condition i.e. test whether the score is >=400 or not.
Step 7: Now if this test is TRUE the result should be “Second”.
4.9 (1,353 ratings)
Step 8: Now the total number IF conditions are 3. If all these IF conditions test is FALSE we need one more condition to test i.e. whether the score is >=300.
Step 9: If this condition is TRUE then the result is “Pass”.
Step 10: Now we came to the last argument. Totally we have entered 4 IF’s, so if all these conditions tests are FALSE then the final result is “FAIL”, so enter “FAIL” as the result.
This is how we can test multiple conditions by nesting many IF condition inside the one IF condition.
The logic here is first IF result will come if the logical test is TRUE if logical test is FALSE then second IF will be executed. Like this, until the formula finds the TRUE test result it will be executed. If none of the results is TRUE then the final FALSE result will be executed.
Example #2 – NESTED IF Function in Excel
Now take look at the real-time corporate example of calculating sales commission. Consider the below data for the example.
In order to arrive the commission %, we need to test the below conditions.
- If the sales value is >=7 lakh, commission % is 10%.
- If the sales value is >=5 lakh, commission % is 7%.
- If the sales value is >=4 lakh, commission % is 5%.
- If the sales value is < 4 lakh, the commission is 0%.
This is very similar to the previous example. Instead of arriving results we need to arrive percentages as the result, let’s apply nested IF Function in excel.
Step 1: Apply IF and test the first condition.
Step 2: Apply second IF, if the first test is FALSE.
Step 3: If the above IF conditions are FALSE then test the third condition.
Step 4: If all the above conditions are FALSE then the result is 0%.
Step 5: Copy down the formula to the remaining cells, we will have results.
Example #3 – NESTED IF Function in Excel
Take an example of how to use other logical function AND with IF condition to test multiple conditions.
Take the same data from the above example, but I have slightly changed the data, I have removed the Sales column.
Here we need to calculate a bonus for these employees based on the below conditions.
- If the employee’s department is Marketing & Year of service is >5 years then the bonus is 50000.
- If the employee’s department is Sales & Year of service is >5 years then the bonus is 45000.
- For all the other employees if the service is > 5 years, the bonus is 25000.
- If the year of service is <5 years, the bonus is zero.
This looks a bit completed, isn’t it?
In order to arrive at a single result, we need to test two conditions. When we need to test two conditions and if both the conditions should be true the AND logical condition will be used.
AND will return the result is TRUE if all the supplied conditions are TRUE. If any of the one condition is FALSE then the result will FALSE only.
Step 1: Open IF condition first.
Step 2: Since we need to test two condition to arrive the result lets open AND function inside the IF condition.
Step 3: Here we need to test the conditions. The first condition is whether the department is Marketing or not and the second condition is a year of service is >=5 years.
Step 4: If the supplied conditions are TRUE bonus amount is 50000.
Step 5: Like this apply tests for remaining conditions. I have already applied the formula to arrive at the results.
Things to Remember
- AND will return the TRUE result if all the supplied conditions are TRUE. If anyone of the condition is FALSE then it will return FALSE as the result.
- In order to arrive the final result, you need to apply one more rather you can pass the result in the FALSE argument only.
Recommended Articles
This has been a guide to Nested IF Function in Excel. Here we discuss how to use Nested IF Function in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
Leave a Reply