Excel Nested If Function

Publication Date :

25 Apr, 2019

Blog Author :

Edited by :

Reviewed by :

Download FREE Nested If Function Excel Template and Follow Along!

Nested IF Condition Excel Template.xlsx

Table Of Contents

What Is Nested IF Function In Excel?

Nested IF function in Excel means we can use another logical or conditional function with the IF function to test more than one condition. For example, suppose there are two conditions to be tested. In that case, we can use the logical AND or OR function depending upon the situation, or we can use the other conditional functions, even more, IFs inside a single IF.

For example, consider the below table showing marks obtained by Billy in various subjects. Now, let us use Nested IF function to find if he has scored pass, fail or distinction.

Excel Nested IF Function - Example 1

The steps are:

Step 1: Select the cell where we want to find the result. In this example, it is cell C2.

Step 2: Now, insert the formula, =IF(B2>=35,"Pass",IF(B2>=40,"DIST",IF(B2<35,"Fail")))

Step 3: Press Enter key.

We can see the result in cell C2. Using AutoFill option, we can obtain results in cell range C3:C7.

Excel Nested IF Function - Example 1-1

Likewise, we can use Nested IF in Excel.

Key Takeaways

Nested IF function in Excel is used to find the outcome using multiple criterias.
IF function has 3 arguments – logical_test, value if true, value if false.
The nested IF function in Excel, in simple terms includes a function within IF function.
This excel function enables users to analyze the data with multiple criterias. For instance, while calculating the age of a population, we can check if the value is “Major”, “Teenager” or “Senior Citizen” using nested IF function in Excel.

Syntax

The syntax of nested IF function in Excel is

IF(logical_test,,,( logical_test,,))

We can use multiple criterias in nested IF function in Excel.

How To Use Nested IF Function In Excel?

We know that the Nested IF function helps users test the data with multiple criterias. Now, let us look at the following examples to understand how to use nested IF function in Excel.

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.”

Now, we have a total of 5 conditions to test. Unfortunately, the logical tests are more than one logical test at the moment. So we need to use Nested IFs to try multiple criteria.

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 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.

Example #2

Now take a look at the real-time corporate example of calculating sales commission. Consider the below data for the example.

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.

Important Things To Note

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.