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