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'.

Practice

1. What does the IFS function do in Excel?

easy

A. Tests multiple conditions and returns the value for the first true one.

B. Adds numbers in a range.

C. Finds the average of numbers.

D. Sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of IFS

   The IFS function checks several conditions one by one.

2. Step 2: Identify the output behavior

   It returns the value linked to the first condition that is true.

3. Final Answer:

   Tests multiple conditions and returns the value for the first true one. -> Option A

4. Quick Check:

   IFS returns first true condition value [OK]

Hint: Remember: IFS stops at first true condition [OK]

Common Mistakes:

• Thinking IFS sums values
• Confusing IFS with AVERAGE
• Assuming IFS sorts data

2. Which of these is the correct syntax for the IFS function in Excel?

easy

A. =IFS(condition1; value1; condition2; value2)

B. =IFS(value1, condition1, value2, condition2)

C. =IFS(condition1, value1, condition2, value2, TRUE, default_value)

D. =IFS(condition1 value1 condition2 value2)

Solution

1. Step 1: Recall IFS syntax rules

   IFS requires pairs: condition, then value, separated by commas.

2. Step 2: Check each option's format

   =IFS(condition1, value1, condition2, value2, TRUE, default_value) uses correct comma separators and pairs conditions with values properly.

3. Final Answer:

   =IFS(condition1, value1, condition2, value2, TRUE, default_value) -> Option C

4. Quick Check:

   Correct IFS syntax uses condition-value pairs separated by commas [OK]

Hint: Pairs: condition then value, separated by commas [OK]

Common Mistakes:

• Using semicolons instead of commas
• Swapping condition and value order
• Missing commas between pairs

3. Given the formula =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F") and cell A1 contains 85, what will be the result?

medium

A. "A"

B. "F"

C. "C"

D. "B"

Solution

1. Step 1: Evaluate conditions in order with A1=85

   Check if 85 > 90 (false), then 85 > 80 (true).

2. Step 2: Return value for first true condition

   Since 85 > 80 is true, return "B" immediately.

3. Final Answer:

   "B" -> Option D

4. Quick Check:

   First true condition value is "B" [OK]

Hint: Check conditions top to bottom, stop at first true [OK]

Common Mistakes:

• Choosing "A" because 85 is close to 90
• Ignoring order of conditions
• Selecting default "F" incorrectly

4. Identify the error in this formula: =IFS(A1>90, "A", A1>80, "B", A1>70, "C") when A1 is 65.

medium

A. Too many conditions for IFS function.

B. Missing a default TRUE condition for fallback value.

C. Conditions are not in descending order.

D. Incorrect use of quotation marks around letters.

Solution

1. Step 1: Check what happens if no condition is true

   With A1=65, none of the conditions (A1>90, A1>80, A1>70) are true.

2. Step 2: Identify missing fallback condition

   Without a final TRUE condition, IFS returns #N/A error when no conditions match.

3. Final Answer:

   Missing a default TRUE condition for fallback value. -> Option B

4. Quick Check:

   Always include TRUE fallback to avoid errors [OK]

Hint: Always end IFS with TRUE for default value [OK]

Common Mistakes:

• Forgetting default TRUE condition
• Thinking order of conditions causes error
• Assuming quotation marks cause error

5. You want to assign grades based on scores in cell B2 using IFS: 90+ = "Excellent", 80-89 = "Good", 70-79 = "Average", below 70 = "Poor". Which formula correctly implements this?

hard

A. =IFS(B2>=90, "Excellent", B2>=80, "Good", B2>=70, "Average", TRUE, "Poor")

B. =IFS(B2>90, "Excellent", B2>80, "Good", B2>70, "Average", TRUE, "Poor")

C. =IFS(B2>=90, "Excellent", B2>80, "Good", B2>70, "Average", TRUE, "Poor")

D. =IFS(B2>90, "Excellent", B2>=80, "Good", B2>=70, "Average", TRUE, "Poor")

Solution

1. Step 1: Understand grade ranges and conditions

   Grades include 90 and above as "Excellent", 80 to 89 as "Good", 70 to 79 as "Average", below 70 as "Poor".

2. Step 2: Check condition operators for correct ranges

   Use >= for lower bounds to include boundary values correctly.

3. Step 3: Verify fallback condition

   TRUE at the end covers scores below 70 with "Poor".

4. Final Answer:

   =IFS(B2>=90, "Excellent", B2>=80, "Good", B2>=70, "Average", TRUE, "Poor") -> Option A

5. Quick Check:

   Use >= for inclusive ranges and TRUE fallback [OK]

Hint: Use >= for boundaries and TRUE last for default [OK]

Common Mistakes:

• Using > instead of >= causing boundary errors
• Missing TRUE fallback condition
• Incorrect order of conditions