Nested IF Formula in Excel

Excel Nested IF Formula (Table of Contents)

• Nested IF Formula in Excel
• How to Use Nested IF Formula in Excel?

Nested IF Formula in Excel

IF Function is one of the most commonly & frequently used logical function in Excel.

Usually, IF function runs a logical test & checks whether a condition or criteria is met or not, and returns one value in a result, it may be either, if true and another value if false, these are the two possible outcomes with if function.

Sometimes you need to work with situations or conditions where there are more than two possible outcomes; in this scenario, the Nested IF Formula helps you out.

Nesting means a combination of formulas, one inside the other, where each formula controls or handles the result of others.

Nested IF Formula is categorized under Advanced IF functions which allow you to check more than one condition.

From excel 2007 version onwards, 64 IF statements or functions can be used in one formula (In Nested IF Formula)

Nested IF Formula: It’s an If function within an if function to test multiple conditions.

Syntax of Nested IF Formula:

=IF(condition, value_if_true1, IF(second condition, value_if_true2, value_if_false2 ))

The Nested IF Formula syntax or formula has the below-mentioned arguments:

• Condition: It is the value that you want to test.
• value_if_true: The value appears or returns if the logical condition evaluates to TRUE.
• value_if_false: The value appears or returns if the logical condition evaluates to FALSE.

Various arithmetic operators that can be used in the Nested IF Formula:

>             Greater Than

=             Equal to

<             Less Than

>=          Greater than or equal to

<=          Less than or equal to

<>          Less than or greater than

These above-mentioned operators are used in the Criteria or Condition argument of Nested IF Formula’s statement; it is purely based on a logic that you apply in the criteria argument.

How to Use NESTED IF Formula in Excel?

Let’s check out how this formula Formula works in Excel

Example #1

Let us analyze the Nested IF Formula with Multiple Criteria.

In the below-mentioned example, the table contains a list of the student in column B (B2 to B18) & the score of each student (C2 to C18).

Here, I want to categorize their scores with the below-mentioned conditions:

DISTINCTION: Over 85.

FIRST CLASS: Between 60 and 84, inclusive.

SECOND CLASS: Between 35 and 59, inclusive.

FAIL: Under 35.

With the above conditions, I need to categorize students’ results based on their score, Here Nested IF Formula. I need to build that formula with multiple IF statements.

Let’s start entering the first IF statement:

=IF(C3>85,”DISTINCTION”,

This takes care of the “DISTINCTION” category students now if I want to handle the second category. “FIRST CLASS”, I need to add another conditional statement:

=IF(C3>=85,”DISTINCTION”, IF(C3>=60, “FIRST CLASS”,

Note: In the above-mentioned syntax, I simply added another IF statement into the first IF statement. Similarly, I extend the formula to handle the next category, “SECOND CLASS”, where I repeat the above-mentioned step once again.

=IF(C3>=85,”DISTINCTION”, IF(C3>=60, “FIRST CLASS”, IF(C3>=35, “SECOND CLASS”,

I continue with similar steps until I reach the last category. Now, I have left with the last category, “FAIL”, if the last category or criteria appears, instead of adding another IF, just I need to add the “FAIL” for a false argument (value_if_false argument).

After entering the last category, you need to close it with three closed brackets.

In the last criteria, value_if_false argument, if the score is less than 35, IF function returns FALSE, as we don’t supply a value if false.

=IF(C3>=85,”DISTINCTION”,IF(C3>=60,”FIRST CLASS”,IF(C3>=35,”SECOND CLASS”,”FAIL”)))

Now, The Nested IF Formula is ready; copy this formula in the cell “D3” and click on enter to get the result. Simultaneously this formula is applied to the whole range by selecting a cell from “D3” to “D18” and click on CTRL + D to get the result.

How Excel Nested IF Logical Test Works?

=IF(C3>=85,”DISTINCTION”,IF(C3>=60,”FIRST CLASS”,IF(C3>=35,”SECOND CLASS”,”FAIL”)))

Now let’s split or break-up the above formula and check out.

=IF(C3>=85,”DISTINCTION”,

IF(C3>=60,”FIRST CLASS”,

IF(C3>=35,”SECOND CLASS”,”FAIL”)))   

Or

IF(check if C3>=85, if true – return “DISTINCTION”, or else

IF(check if C3>=60, if true – return “FIRST CLASS”, or else

IF(check if C3>=35, if true – return “SECOND CLASS”, if false –

return “FAIL”)))

Here, the Nested IF formula actually directs the excel to evaluate the logical test for the first IF function; in the result, if the condition or criteria is met, then it returns the supplied value (“DISTINCTION”) in the value_if_true argument. Otherwise or else, If the condition or criteria of the first If a function is not met, then go ahead and carry out or test the second If statement and follow the similar step until the last criteria.

Conclusion

In the below-mentioned screenshot, you can observe, usually, parenthesis pairs are shaded in various or different colors so that the opening parenthesis matches the closing one at the end.

• Text values should always be enclosed in double-quotes “DISTINCTION.”

Note: Never present the numbers or numeric values in the double quotes, e.g. 85, 60 & 35

• You can add line break or spaces in the formula bar to understand this Formula in a better & easier way.
• Evaluate Formula feature is located on the Formula tab under the Formula Auditing group or subsection. This feature helps out to solve your complex formula. Nested IF Formula step by step.

When you click on the evaluate button frequently, it will show you all the evaluation process steps (From beginning to end, how the formula works step by step process).

Things to Remember

• When using the Nested IF Formula, we should not start the second criteria in the IF function with the = sign.
• Apart from arithmetic operators, you can also use addition, subtraction, multiplication & division symbols, e.g. =IF(C1<10, C1*4,
• The order of IF statements in the Nested IF Formula is very important to evaluate the logical test. If the IF function’s first condition evaluates TRUE, then subsequent conditions or IF statements don’t work. The formula stops at first result TRUE.
• Parenthesis Match: It is an important Criteria in the Nested IF formula; if the parentheses do not match, then the Nested IF formula won’t work.

Recommended Articles

This has been a guide to Nested IF Formula in Excel. Here we discuss How to use the Nested IF Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles-

1. COUNTIF Formula in Excel
2. COUNTIF Excel Function
3. Excel IF Function
4. Excel IF AND Function