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