Nested IF Statements in Advanced Excel

A nested IF statement in Excel is an IF function inside another IF function. This allows you to evaluate multiple conditions and return different results based on those conditions. Nested IF statements are useful when you have more than two possible outcomes, and they can be used to handle complex logical tests.

What is a Nested IF Statement?

A nested IF function is simply an IF function placed inside another IF function. Each IF function will evaluate a condition and return a result, and if the first condition is false, it will evaluate the next condition.

Syntax of Nested IF: IF(condition1, value_if_true, IF(condition2, value_if_true2, value_if_false2))

Real-Time Example 1: Grading System

Scenario: You want to assign grades based on a student's score. The grading system is as follows:

  • 90 and above: A
  • 80 to 89: B
  • 70 to 79: C
  • Below 70: F

Steps:

  1. In cell B2, enter the student's score (e.g., 85).
  2. In cell C2, enter the following formula to calculate the grade based on the score: 
    3. =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", "F")))
  3. Explanation: The formula checks if the score is 90 or more. If true, it returns "A". If false, it checks if the score is between 80 and 89 for a "B", then checks for a "C", and if none of the conditions are met, it returns "F".
  4. If you enter a score of 85 in cell B2, the result in cell C2 will be "B".

Real-Time Example 2: Employee Bonus

Scenario: You want to calculate the bonus for employees based on their performance and years of service. The bonus calculation is as follows:

  • If an employee has worked for 5 or more years and has a performance rating of "Excellent", they receive a 10% bonus.
  • If the performance rating is "Good", they receive a 5% bonus, regardless of the years of service.
  • If neither condition is met, the employee does not receive a bonus.

Steps:

  1. In cell A2, enter the number of years the employee has worked (e.g., 6).
  2. In cell B2, enter the performance rating (e.g., "Excellent").
  3. In cell C2, enter the following formula to calculate the bonus:
    4. =IF(A2>=5, IF(B2="Excellent", "10% Bonus", "No Bonus"), IF(B2="Good", "5% Bonus", "No Bonus"))
  4. Explanation: The formula first checks if the employee has worked for 5 or more years. If true, it checks if the performance rating is "Excellent" to assign a 10% bonus. If not "Excellent", it assigns "No Bonus". If the employee has worked less than 5 years, it checks if the performance rating is "Good" to assign a 5% bonus. If none of these conditions are met, it returns "No Bonus".
  5. If you enter "6" in cell A2 and "Excellent" in cell B2, the result in cell C2 will be "10% Bonus".

Real-Time Example 3: Shipping Charges

Scenario: A shipping company uses the following rules for calculating shipping charges:

  • If the weight of the item is less than 5kg, the shipping charge is $10.
  • If the weight is between 5kg and 20kg, the shipping charge is $20.
  • If the weight is more than 20kg, the shipping charge is $50.

Steps:

  1. In cell A2, enter the weight of the item (e.g., 8).
  2. In cell B2, enter the following formula to calculate the shipping charge:
    3. =IF(A2<5, "$10", IF(A2<=20, "$20", "$50"))
  3. Explanation: The formula checks if the weight is less than 5kg. If true, it returns "$10". If false, it checks if the weight is between 5 and 20kg to return "$20". If neither condition is met, it returns "$50" for items heavier than 20kg.
  4. If you enter 8 in cell A2, the result in cell B2 will be "$20".

Real-Time Example 4: Discount Calculation

Scenario: A store offers discounts on purchases based on the total amount spent. The rules are as follows:

  • Spending over $500 qualifies for a 20% discount.
  • Spending between $200 and $500 qualifies for a 10% discount.
  • Spending below $200 qualifies for no discount.

Steps:

  1. In cell A2, enter the total spending amount (e.g., 350).
  2. In cell B2, enter the following formula to calculate the discount:
    3. =IF(A2>500, "20% Discount", IF(A2>=200, "10% Discount", "No Discount"))
  3. Explanation: The formula first checks if the spending amount is greater than $500. If true, it returns a 20% discount. If false, it checks if the spending amount is between $200 and $500 to apply a 10% discount. If neither condition is true, it returns "No Discount".
  4. If you enter 350 in cell A2, the result in cell B2 will be "10% Discount".

Real-Time Example 5: Age Group Categorization

Scenario: You want to categorize people into age groups based on their age:

  • Under 18: "Child"
  • 18 to 35: "Young Adult"
  • 36 to 60: "Adult"
  • Above 60: "Senior"

Steps:

  1. In cell A2, enter the person's age (e.g., 25).
  2. In cell B2, enter the following formula to categorize the person:
    3. =IF(A2<18, "Child", IF(A2<=35, "Young Adult", IF(A2<=60, "Adult", "Senior")))
  3. Explanation: The formula checks if the age is less than 18 to return "Child", then checks if the age is between 18 and 35 for "Young Adult", then checks if it is between 36 and 60 for "Adult", and if none of these conditions are true, it returns "Senior".
  4. If you enter 25 in cell A2, the result in cell B2 will be "Young Adult".

Conclusion

Nested IF statements in Excel are a powerful tool for evaluating multiple conditions. By using them, you can create complex decision-making processes and handle various scenarios in your data. Nested IF statements can be combined with other Excel functions, like AND(), OR(), and NOT(), to create even more powerful formulas. With practice, you can master nested IF functions to solve a wide range of problems in Excel.




Advertisement