Excel Nested If Function How To Use Nested If Functions Examples

Nested IF Function in Excel

For example, suppose we have a dataset consisting of the age and names of employees. We need to check if any employees are less than the required eligibility to work, i.e., 20 years. We can use the Nested IF function in Excel in such a scenario. Using this function may display “TRUE” if it met with the first condition, i.e., age is more or equal to 20 years. Conversely, it may indicate “FAIL” if it does not satisfy the first condition. If the logical test is “FALSE,” we can execute the second IF. It will perform until the formula finds the “TRUE” test result. If none of the results is “TRUE,” it will execute the final “FALSE” result.

Examples

The following examples are used to calculate the Nested IF function in Excel:

Example #1

Now take a look at the popular nested IF example. We need to arrive at standards based on the student’s score. Consider the below data for an example.

To arrive at the results, we need to test the below conditions. 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,” the result should be “FAIL.”

Example #2

Now take a look at the real-time corporate example of calculating sales commissionSales CommissionSales commission is a monetary reward awarded by companies to the sales reps who have managed to achieve their sales target. It is an incentive geared towards producing more sales and rewarding the performers while simultaneously recognizing their efforts. A sales commission agreement is signed to agree on the terms and conditions set for eligibility to earn a commission.read more. Consider the below data for the example.

We must open the IF condition and pass the first test, i.e., whether the score is >=585. If the above logical test is “TRUE,” we need the result as “Dist.” So, we must enter the result in double-quotes. The next argument is if the value or test is “FALSE.” If the test is “FALSE,” we have four more conditions to test, so we must open one more IF condition in Excel in the next argument. Now, let us test the second condition here. The second condition is to test whether the score is >= 500. So, we must pass the argument as >=500. If this test is true the result should be “First”. So, enter the result in double-quotes. 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 u003e=400 or not. If this test is “TRUE,” the result should be “Second.” Now the total number of IF conditions is 3. So, if all these IF conditions test is “FALSE,” we need one more condition to test, i.e., whether the score is >=300. If this condition is “TRUE,” the result is “Pass.” Now we came to the last argument. We have entered four IFs, so if all these conditions tests are “FALSE,” then the final result is “FAIL,” so enter “FAIL” as a result. Like this, we can test multiple conditions by nesting many IF conditions inside the one IF condition.The logic here is the first IF result will come if the logical test is “TRUE.” If the logical test is “FALSE,” then the second IF can be executed. Until the formula finds the “TRUE” test result, it will execute it. If none of the results is “TRUE,” it will execute the final “FALSE” result.

Like this, we can test multiple conditions by nesting many IF conditions inside the one IF condition.The logic here is the first IF result will come if the logical test is “TRUE.” If the logical test is “FALSE,” then the second IF can be executed. Until the formula finds the “TRUE” test result, it will execute it. If none of the results is “TRUE,” it will execute the final “FALSE” result.

To arrive at the commission %, we need to test the below conditions.

If the sales value is >=7 lakhs, commission % is 10%.If the sales value is >=5 lakhs, commission % is 7%.If the sales value is >=4 lakhs, commission % is 5%.If the sales value is < 4 lakhs, the commission is 0%.

It is similar to the previous example. However, instead of arriving at results, we need to reach percentages. Let us apply the nested IF function in Excel.

Step 1: We must first apply IF and test the first condition.
Step 2: Then, we must use the second IF condition if the first test is “FALSE.”
Step 3: If the above IF conditions are “FALSE,” test the third condition.
Step 4: If all the above conditions are “FALSE, ” the result is 0%.
Step 5: After that, we must copy the formula to the remaining cells. Consequently, it will provide us with the results.

Example #3

Take how to use other logical functions: AND with IF condition to test multiple conditions.

Take the same data from the above example. But we have slightly changed the data. We 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” and “Years of Service” is >5 years then the bonus is 50,000.If the employee’s department is “Sales” and “ Years of Service” is >5 years then the bonus is 45,000.If the service is > 5 years for all the other employees, the bonus is 25,000.If the year of service is <5 years, the bonus is zero.

It looks a bit completed.

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 can be used.

The Excel AND function will return the “TRUE” result if all the supplied conditions are “TRUE.” If one condition is “FALSE,” the result will be only “FALSE.”

Step 1: We must open the IF condition first.
Step 2: Since we need to test two conditions to arrive at the result, let’s 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 “Years of Service” is >=5 years.
Step 4: If the supplied conditions are “TRUE,” the bonus amount is 50000.
Step 5: We must apply tests for the remaining conditions like this. We have already used the formula to arrive at the results.

Things to Remember

The Excel AND function will return the “TRUE” result if all the supplied conditions are “TRUE.” If any of the conditions is “FALSE,” it will return “FALSE.”To arrive at the final result, we must apply one more rather. Then, we can pass the result in the “FALSE” argument only.

Nested IF Function in Excel#

Examples#

Example #1#

Example #2#

Example #3#

Things to Remember#

Recommended Articles#