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.