Nested IF functions in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to categorize sales performance of salespeople based on their monthly sales figures.

📊 Data: You have a list of salespeople with their monthly sales amounts in dollars.

🎯 Deliverable: Create a new column that uses nested IF functions to assign performance categories: 'Excellent' for sales above 10000, 'Good' for sales between 7000 and 10000, 'Average' for sales between 4000 and 7000, and 'Needs Improvement' for sales below 4000.

Sample Data

Salesperson	Monthly Sales
Alice	12000
Bob	8500
Charlie	6700
Diana	3900
Edward	15000
Fiona	7200
George	3000
Hannah	9800

1

Step 1: Insert a new column next to 'Monthly Sales' and name it 'Performance Category'.

Expected Result

A new empty column titled 'Performance Category' appears next to 'Monthly Sales'.

2

Step 2: In the first cell under 'Performance Category' (assume C2), enter the nested IF formula to categorize sales.

=IF(B2>10000,"Excellent",IF(B2>=7000,"Good",IF(B2>=4000,"Average","Needs Improvement")))

Expected Result

For Alice with 12000 sales, the formula returns 'Excellent'.

3

Step 3: Copy the formula from C2 down to all rows in the 'Performance Category' column.

Drag the fill handle from C2 down to C9.

Expected Result

Each salesperson's sales are categorized correctly: Bob 'Good', Charlie 'Average', Diana 'Needs Improvement', Edward 'Excellent', Fiona 'Good', George 'Needs Improvement', Hannah 'Good'.

Final Result

Salesperson | Monthly Sales | Performance Category
------------|---------------|---------------------
Alice       | 12000         | Excellent
Bob         | 8500          | Good
Charlie     | 6700          | Average
Diana       | 3900          | Needs Improvement
Edward      | 15000         | Excellent
Fiona       | 7200          | Good
George      | 3000          | Needs Improvement
Hannah      | 9800          | Good

✓Salespeople with sales above 10000 are categorized as Excellent.

✓Sales between 7000 and 10000 are categorized as Good.

✓Sales between 4000 and 7000 are categorized as Average.

✓Sales below 4000 are categorized as Needs Improvement.

Bonus Challenge

Modify the nested IF formula to also highlight 'Excellent' salespeople with bold text using conditional formatting.

Show Hint

Use Excel's Conditional Formatting feature with a formula rule: =B2>10000 and set the font to bold.

Practice

1. What does a nested IF function in Excel do?
=IF(A1>10, "High", IF(A1>5, "Medium", "Low"))

easy

A. It checks multiple conditions one after another and returns different results.

B. It adds numbers in multiple cells.

C. It sorts data in ascending order.

D. It creates a chart based on cell values.

Solution

1. Step 1: Understand the IF function

   The IF function checks a condition and returns one value if TRUE, another if FALSE.

2. Step 2: See how nesting works

   The nested IF checks the first condition; if FALSE, it checks the next condition inside another IF.

3. Final Answer:

   It checks multiple conditions one after another and returns different results. -> Option A

4. Quick Check:

   Nested IF = multiple condition checks [OK]

Hint: Think of nested IF as a decision tree [OK]

Common Mistakes:

• Confusing IF with SUM or other functions
• Thinking nested IF adds or sorts data
• Ignoring the order of conditions

2. Which of the following is the correct syntax for a nested IF function in Excel?

easy

A. =IF(A1>10, "High", IF A1>5, "Medium", "Low")

B. =IF(A1>10, "High", IF(A1>5, "Medium", "Low"))

C. =IF(A1>10, "High"; IF(A1>5, "Medium", "Low"))

D. =IF(A1>10, "High", ELSE IF(A1>5, "Medium", "Low"))

Solution

1. Step 1: Review correct IF syntax

   IF syntax requires parentheses enclosing condition, true result, and false result separated by commas.

2. Step 2: Check each option

   =IF(A1>10, "High", IF(A1>5, "Medium", "Low")) uses commas and proper parentheses; others use ELSE IF, semicolons, or missing parentheses which are incorrect.

3. Final Answer:

   =IF(A1>10, "High", IF(A1>5, "Medium", "Low")) -> Option B

4. Quick Check:

   Correct IF syntax uses commas and parentheses [OK]

Hint: Remember: IF uses commas, not semicolons or ELSE [OK]

Common Mistakes:

• Using ELSE IF instead of nested IF
• Replacing commas with semicolons
• Missing parentheses around nested IF

3. Given the formula =IF(B2>90, "A", IF(B2>75, "B", IF(B2>60, "C", "F"))) and B2 = 80, what will be the result?

medium

A. A

B. C

C. B

D. F

Solution

1. Step 1: Check first condition B2>90

   Since B2 is 80, 80>90 is FALSE, so move to next IF.

2. Step 2: Check second condition B2>75

   80>75 is TRUE, so return "B" without checking further.

3. Final Answer:

   B -> Option C

4. Quick Check:

   80 is between 75 and 90, so grade = B [OK]

Hint: Check conditions top-down; first TRUE stops checking [OK]

Common Mistakes:

• Choosing 'A' because 80 is close to 90
• Ignoring order of conditions
• Picking 'C' or 'F' without checking all IFs

4. Identify the error in this nested IF formula:
=IF(A1>100, "High", IF(A1>50, "Medium", "Low"

medium

A. Extra comma after "Medium".

B. Using wrong comparison operator.

C. Incorrect use of quotation marks.

D. Missing closing parenthesis for the second IF function.

Solution

1. Step 1: Count parentheses

   The formula has one opening IF with two conditions but only one closing parenthesis at the end.

2. Step 2: Identify missing parenthesis

   The second IF function is missing its closing parenthesis, causing a syntax error.

3. Final Answer:

   Missing closing parenthesis for the second IF function. -> Option D

4. Quick Check:

   Every IF needs matching parentheses [OK]

Hint: Count parentheses pairs carefully [OK]

Common Mistakes:

• Assuming commas or quotes are wrong
• Not noticing missing parentheses
• Thinking comparison operators cause error

5. You want to assign a category in cell C2 based on sales in B2:
- "Excellent" if sales >= 1000
- "Good" if sales >= 500 but < 1000
- "Average" if sales >= 100 but < 500
- "Poor" if sales < 100
Which nested IF formula correctly assigns these categories?

hard

A. =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2>=100, "Average", "Poor")))

B. =IF(B2>=1000, "Excellent", IF(B2>=100, "Average", IF(B2>=500, "Good", "Poor")))

C. =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2<=100, "Poor", "Average")))

D. =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2>=100, "Poor", "Average")))

Solution

1. Step 1: Check conditions order

   Conditions must be checked from highest to lowest to avoid misclassification.

2. Step 2: Verify each nested IF matches category ranges

   =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2>=100, "Average", "Poor"))) checks >=1000, then >=500, then >=100, else "Poor" which matches the problem exactly.

3. Final Answer:

   =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2>=100, "Average", "Poor"))) -> Option A

4. Quick Check:

   Highest to lowest conditions in nested IF [OK]

Hint: Order conditions from largest to smallest [OK]

Common Mistakes:

• Checking smaller values before larger ones
• Mixing up category labels
• Using wrong comparison operators