IFS function (multiple conditions) 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 amounts into performance levels: 'Low', 'Medium', 'High', and 'Very High' based on specific sales thresholds.

📊 Data: You have a list of sales transactions with sales amounts in dollars.

🎯 Deliverable: Create a new column that uses the IFS function to assign the correct performance level to each sales amount.

Sample Data

Transaction ID	Sales Amount
1001	450
1002	1200
1003	700
1004	2500
1005	150
1006	3200
1007	900
1008	50

1

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

Expected Result

A new empty column labeled 'Performance Level' appears next to 'Sales Amount'.

2

Step 2: In the first cell under 'Performance Level' (assume C2), enter the IFS formula to categorize sales amounts: 'Low' for sales less than 500, 'Medium' for sales between 500 and 999, 'High' for sales between 1000 and 1999, and 'Very High' for sales 2000 or more.

=IFS(B2<500, "Low", AND(B2>=500, B2<1000), "Medium", AND(B2>=1000, B2<2000), "High", B2>=2000, "Very High")

Expected Result

For the first row with sales 450, the formula returns 'Low'.

3

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

Drag the fill handle from C2 down to C9.

Expected Result

Each sales amount is categorized correctly: 450=Low, 1200=High, 700=Medium, 2500=Very High, 150=Low, 3200=Very High, 900=Medium, 50=Low.

Final Result

Transaction ID | Sales Amount | Performance Level
-------------- | ------------ | -----------------
1001           | 450          | Low
1002           | 1200         | High
1003           | 700          | Medium
1004           | 2500         | Very High
1005           | 150          | Low
1006           | 3200         | Very High
1007           | 900          | Medium
1008           | 50           | Low

✓Sales below 500 are categorized as Low performance.

✓Sales between 500 and 999 are Medium performance.

✓Sales between 1000 and 1999 are High performance.

✓Sales 2000 and above are Very High performance.

Bonus Challenge

Modify the IFS formula to include a category 'No Sales' for sales amounts equal to zero or blank cells.

Show Hint

Add a condition at the start of the IFS formula to check if the sales amount is zero or empty using OR(B2=0, ISBLANK(B2)) and assign 'No Sales'.