IFS function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of IFS with multiple conditions

Given the formula =IFS(A1 < 50, "Low", A1 < 80, "Medium", A1 < 100, "High") and the value in cell A1 is 75, what will be the output?

A "Low"

B "High"

C "Medium"

D Error

💡 Hint

Think about which condition is true first when checking from top to bottom.

The IFS function checks conditions in order. Since 75 is not less than 50, it skips the first condition. The second condition (A1 < 80) is true, so it returns "Medium" and stops checking further.

❓ Function Choice

intermediate

Choosing the correct IFS formula for grading

You want to assign grades based on scores in cell B2: 90 and above is "A", 80 to 89 is "B", 70 to 79 is "C", below 70 is "F". Which IFS formula correctly implements this?

A =IFS(B2 > 90, "A", B2 >= 80, "B", B2 >= 70, "C", B2 <= 70, "F")

B =IFS(B2 > 90, "A", B2 > 80, "B", B2 > 70, "C", B2 <= 70, "F")

C =IFS(B2 >= 90, "A", B2 > 80, "B", B2 > 70, "C", B2 < 70, "F")

D =IFS(B2 >= 90, "A", B2 >= 80, "B", B2 >= 70, "C", B2 < 70, "F")

💡 Hint

Remember to use >= for the lower bound of each grade range and order conditions from highest to lowest.

Option D correctly uses >= for the lower bounds and orders conditions from highest to lowest, ensuring correct grade assignment. Other options have incorrect comparison operators or order that cause wrong results.

❓ data_analysis

advanced

Analyzing IFS formula with overlapping conditions

Consider the formula =IFS(C1 >= 100, "Excellent", C1 >= 80, "Good", C1 >= 80, "Average", TRUE, "Poor"). What will be the output if C1 is 85?

A "Average"

B "Good"

C "Poor"

D Error

💡 Hint

Check the order of conditions and which one matches first.

The IFS function evaluates conditions in order. The first condition is false, the second condition (C1 >= 80) is true, so it returns "Good" and stops. The repeated condition for "Average" is never reached.

🎯 Scenario

advanced

Using IFS to categorize sales performance

You have monthly sales in cell D5. You want to categorize sales as "Low" if less than 500, "Moderate" if between 500 and 999, "High" if between 1000 and 1499, and "Very High" if 1500 or more. Which formula correctly categorizes the sales?

A =IFS(D5 < 500, "Low", D5 < 1000, "Moderate", D5 < 1500, "High", D5 >= 1500, "Very High")

B =IFS(D5 <= 499, "Low", D5 >= 500, "Moderate", D5 >= 1000, "High", D5 >= 1500, "Very High")

C =IFS(D5 < 500, "Low", D5 >= 500, "Moderate", D5 >= 1000, "High", D5 >= 1500, "Very High")

D =IFS(D5 <= 500, "Low", D5 <= 999, "Moderate", D5 <= 1499, "High", D5 > 1500, "Very High")

💡 Hint

Order conditions from smallest to largest and use less than for upper bounds.

Option A correctly uses less than for upper bounds and orders conditions so each range is exclusive and covers all values. Other options have overlapping or incorrect ranges.

📊 Formula Result

expert

Result of IFS with logical TRUE as last condition

What is the output of the formula =IFS(E1 < 0, "Negative", E1 = 0, "Zero", TRUE, "Positive") when E1 contains the value -5?

A "Negative"

B "Zero"

C "Positive"

D Error

💡 Hint

Check which condition matches first for the value -5.

The first condition E1 < 0 is true for -5, so the formula returns "Negative" immediately without checking further conditions.