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"))



ADVERTISEMENT
ADVERTISEMENT