Question

Discuss nested IF functions, giving an example.

Answer 1

A Nested IF function is an advanced formula structure in Excel created by placing one or more IF functions inside another IF function. It is used when a business scenario has multiple conditions to test and requires more than just two possible outcomes (True or False). Excel tests the first condition; if it is true, it stops and returns the result. If it is false, it moves inward to test the next IF condition in the chain.

The standard syntax for a nested IF with three possible outcomes is written as:

= IF(conditionl, value_if_truel, IF(condition2, value_if_true2, value_if_false2))

Example: (Employee Bonus Grading)

Suppose a firm wants to automatically calculate a performance grade for its sales employees based on their monthly sales target achievements in cell B2:

• If Sales are Rs. 80,000 or more, the grade is “Excellent”.
• If Sales are Rs. 50,000 or more (but less than 80,000), the grade is “Good”.
• If Sales are less than Rs. 50,000, the grade is “Average”.

To automate this multiple-tier grading, you enter the following formula in cell C2:

= IF(B2> = 80000, “Excellent”, IF(B2> = 50000, “Good”, “Average”)

Excel Processes:

• Scenario A (If B2 contains 85,000): Excel checks the first condition (85,000 ≥ 80,000). This is True, so it instantly displays “Excellent” and stops checking.
• Scenario B (If B2 contains 60,000): Excel checks the first condition (60,000 ≥ 80,000). This is False, so it jumps to the second IF. It checks if 60,000 ≥ 50,000, which is True, and displays “Good”. 
• Scenario C (If B2 contains 40,000): Both the first and second conditions fail, so Excel automatically falls back to the final default value and displays “Average”.