Excel Nested IF’s Function
Excel Nested IF’s Function
This function in excel helps in checking multiple conditions together by using IF conditions within the IF condition.
Syntax
if(Logical_Test, [value_if_true], if(Logical_Test,[value_if_true]
, [value_if_false]))
Parameter
Logical_Test (required)- This parameter represents the condition which you want to check (greater than, less than or equal to signs can be used for numbers). When you want to evaluate a condition based on text value, then use double quotes ('') with equal to signs.
value_if_true (optional)- This parameter represents the logical test value, and if it holds the value, then excel will return this value.
value_if_false (optional)- If the logical test does not hold, then excel will return this value.
Nested IF – It represents the various if conditions. From Excel 2007 onwards, we can use 64 IF conditions in one formula.
Category
Logical
Return
The function returns one value if the condition is TRUE and another value if it's FALSE.
Example 1
Objective: Return result of students based on the Criterion given on the right side
| Criteria | Status |
| 0 – 99 | Fail |
| 100 – 150 | Pass |
| 150+ | Excellent |
| Name | Final Score | Result | Formula Text |
| Thomas E. Edison | 143 | Pass | =IF(C178>150,"Excellent",IF(C178<99,"Fail","Pass")) |
| Shoaib Aktar | 91 | Fail | =IF(C179>150,"Excellent",IF(C179<99,"Fail","Pass")) |
| Gerard Das | 107 | Pass | =IF(C180>150,"Excellent",IF(C180<99,"Fail","Pass")) |
| Itrat Sheen | 150 | Pass | =IF(C181>150,"Excellent",IF(C181<99,"Fail","Pass")) |
| Jack gill | 151 | Excellent | =IF(C182>150,"Excellent",IF(C182<99,"Fail","Pass")) |
| Willian Stone | 139 | Pass | =IF(C183>150,"Excellent",IF(C183<99,"Fail","Pass")) |
| Joe Whiteson | 178 | Excellent | =IF(C184>150,"Excellent",IF(C184<99,"Fail","Pass")) |
| Ronald J. Carlson | 120 | Pass | =IF(C185>150,"Excellent",IF(C185<99,"Fail","Pass")) |
| Anie Jack | 79 | Fail | =IF(C186>150,"Excellent",IF(C186<99,"Fail","Pass")) |
| Elmer C. Laslo | 128 | Pass | =IF(C187>150,"Excellent",IF(C187<99,"Fail","Pass")) |
Example 2
Objective: Use Nested IF to put the grade a student gets in an exam. Score Ranges for different grades are given in criteria.
| Criteria | Grade |
| > 75 | A |
| 41-75 | B |
| < 40 | C |
| Student ID | Marks | Grade | Formula |
| S001 | 90 | A | =IF(E26< 41,"C",IF(E26< 75,"B","A")) |
| S002 | 70 | B | =IF(E27< 41,"C",IF(E27< 75,"B","A")) |
| S003 | 20 | C | =IF(E28< 41,"C",IF(E28< 75,"B","A")) |
| S004 | 41 | B | =IF(E29< 41,"C",IF(E29< 75,"B","A")) |