Excel Nested IF Formula (Table of Contents)
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, Nested IF Formula helps you out.
Nesting means to 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 below-mentioned arguments:
- Condition: It is the value which 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 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 Criteria or Condition argument of if statement in Nested IF Formula, it is purely based on logic what you apply in 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 Nested IF Formula with Multiple Criteria.
In the below-mentioned example, Table contains a list of the student in column B (B2 to B18) & 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 student’s results based on their score, Here Nested IF Formula helps out. 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 “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 so on 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 be always 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 steps in the evaluation process (From beginning to end, how the formula works step by step process).
Things to Remember
- When using Nested IF Formula, we should not start the second criteria in IF function with = sign
- Apart from arithmetic operators, you can also use addition, subtraction, multiplication & division symbols eg. =IF(C1<10, C1*4,
- The order of IF statements in Nested IF Formula us very important to evaluate the logical test. if the first condition of IF function evaluates to TRUE, then subsequent conditions or IF statement doesn’t work. The formula stops at first result TRUE.
- Parenthesis Match: It is an important Criteria in Nested IF formula if the parentheses do not match, then 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 Nested IF Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles-
