IFS function (multiple conditions) in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of IFS with multiple conditions

What is the result of this formula if cell A1 contains 75?

=IFS(A1 < 50, "Fail", A1 < 70, "Pass", A1 < 90, "Merit", TRUE, "Distinction")

A "Fail"

B "Distinction"

C "Pass"

D "Merit"

💡 Hint

Remember IFS checks conditions in order and returns the first TRUE result.

The formula checks conditions in order: 75 is not less than 50 (false), not less than 70 (false), but less than 90 (true), so it returns "Merit".

❓ Function Choice

intermediate

Choose the correct IFS formula for grading

Which formula correctly assigns grades based on score in A1:
- Below 60: "Fail"
- 60 to 79: "Pass"
- 80 to 89: "Merit"
- 90 and above: "Distinction"

A =IFS(A1 < 60, "Fail", A1 < 80, "Pass", A1 < 90, "Merit", TRUE, "Distinction")

B =IFS(A1 < 60, "Fail", A1 < 79, "Pass", A1 < 89, "Merit", TRUE, "Distinction")

C =IFS(A1 < 60, "Fail", A1 < 80, "Pass", A1 < 89, "Merit", TRUE, "Distinction")

D =IFS(A1 < 60, "Fail", A1 < 79, "Pass", A1 < 90, "Merit", TRUE, "Distinction")

💡 Hint

Check the upper limits carefully for each grade range.

Option A correctly uses < 80 for Pass (up to 79), < 90 for Merit (up to 89), and TRUE for Distinction (90 and above). Others have incorrect upper bounds.

❓ data_analysis

advanced

Count how many students got Merit

Given scores in cells A2:A10, which formula counts how many students scored a Merit grade using IFS logic?
Merit is for scores 80 to 89 inclusive.

A =COUNTIF(A2:A10, "IFS(A2:A10>=80, A2:A10<90)")

B =SUMPRODUCT(--(A2:A10>=80), --(A2:A10<90))

C =COUNTIFS(A2:A10, ">=80", A2:A10, "<90")

D =COUNTIF(A2:A10, ">=80") - COUNTIF(A2:A10, ">=90")

💡 Hint

Think about how to count numbers between two values.

Option C uses COUNTIFS to count values >=80 and <90, matching Merit range. Option C also works but is more complex. Option C works but is less direct. Option C is invalid syntax.

🎯 Scenario

advanced

Identify the error in this IFS formula

What error will this formula produce if entered in Excel?

=IFS(A1 < 50, "Fail", A1 < 70, "Pass", A1 < 90, "Merit")
Assume A1 contains 95.

A #N/A error because no condition is TRUE and no default is provided

B Returns "Fail" because first condition is always checked

C Returns "Merit" because last condition covers all remaining values

D Formula returns 0 because no TRUE condition

💡 Hint

What happens if none of the conditions are TRUE in IFS?

IFS requires at least one condition to be TRUE or a default TRUE condition at the end. Without it, if no condition matches, Excel returns #N/A error.

📊 Formula Result

expert

Complex nested IFS with text and numbers

What is the result of this formula if B1 contains "Yes" and C1 contains 45?

=IFS(B1="No", "No action", AND(B1="Yes", C1 < 50), "Low", AND(B1="Yes", C1 >= 50), "High", TRUE, "Unknown")

A "Unknown"

B "Low"

C "No action"

D "High"

💡 Hint

Check each condition carefully. Excel uses the AND function to combine multiple conditions.

First condition B1="No" is FALSE. Second condition AND(B1="Yes", C1<50) is TRUE (B1 matches and 45<50), so formula returns "Low".

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