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.

Practice

1. What does the IFS function do in Google Sheets?

easy

A. Sorts data alphabetically.

B. Checks multiple conditions and returns the value for the first true condition.

C. Adds numbers in a range.

D. Counts the number of cells with numbers.

Solution

1. Step 1: Understand the purpose of IFS

   The IFS function evaluates multiple conditions one by one.

2. Step 2: Identify what IFS returns

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

3. Final Answer:

   Checks multiple conditions and returns the value for the first true condition. -> Option B

4. Quick Check:

   IFS returns first true condition's value [OK]

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

Common Mistakes:

• Thinking IFS sums values
• Confusing IFS with sorting or counting
• Assuming IFS returns all true values

2. Which of the following is the correct syntax for the IFS function in Google Sheets?

easy

A. IFS(condition1: value1, condition2: value2)

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

C. IFS(condition1; value1; condition2; value2)

D. IFS(condition1, value1, condition2, value2, ..., TRUE, default_value)

Solution

1. Step 1: Recall IFS syntax format

   IFS requires pairs of conditions and values separated by commas.

2. Step 2: Identify correct option

   IFS(condition1, value1, condition2, value2, ..., TRUE, default_value) correctly shows pairs and includes a default TRUE condition.

3. Final Answer:

   IFS(condition1, value1, condition2, value2, ..., TRUE, default_value) -> Option D

4. Quick Check:

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

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

Common Mistakes:

• Using semicolons instead of commas
• Swapping condition and value order
• Using colons instead of commas

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

B. "F"

C. "C"

D. "A"

Solution

1. Step 1: Evaluate conditions in order

   Check if A1>90: 85>90 is false, move to next.

2. Step 2: Check second condition

   Check if A1>80: 85>80 is true, so return "B".

3. Final Answer:

   "B" -> Option A

4. Quick Check:

   First true condition returns "B" [OK]

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

Common Mistakes:

• Choosing the highest grade instead of first true
• Ignoring order of conditions
• Forgetting TRUE default case

4. Identify the error in this formula: =IFS(A1>50, "Pass", A1<50, "Fail")

medium

A. Conditions are reversed; should be A1<50 first.

B. Quotes around text values are incorrect.

C. Missing default TRUE condition for unmatched cases.

D. IFS cannot compare numbers.

Solution

1. Step 1: Analyze conditions coverage

   Conditions check if A1>50 or A1<50 but miss A1=50 case.

2. Step 2: Identify missing default case

   Without a TRUE default, formula returns #N/A if no condition matches.

3. Final Answer:

   Missing default TRUE condition for unmatched cases. -> Option C

4. Quick Check:

   Always add TRUE default to handle all cases [OK]

Hint: Add TRUE default condition to avoid errors [OK]

Common Mistakes:

• Not including a TRUE default condition
• Assuming all cases covered without equality
• Misunderstanding error messages

5. You want to assign a category based on sales in cell B2: "High" if sales > 1000, "Medium" if sales between 500 and 1000, "Low" if sales between 100 and 499, and "None" if sales below 100. Which IFS formula is correct?

hard

A. =IFS(B2>1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None")

B. =IFS(B2>1000, "High", B2>500, "Medium", B2>100, "Low", TRUE, "None")

C. =IFS(B2>=1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None")

D. =IFS(B2>1000, "High", B2>499, "Medium", B2>99, "Low", TRUE, "None")

Solution

1. Step 1: Check condition ranges carefully

   High is sales > 1000, Medium is sales between 500 and 1000 inclusive, Low is 100 to 499 inclusive.

2. Step 2: Verify conditions order and boundaries

   =IFS(B2>1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None") correctly uses >1000, then >=500, then >=100, and finally TRUE for below 100.

3. Final Answer:

   =IFS(B2>1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None") -> Option A

4. Quick Check:

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

Hint: Use >= for lower bounds and TRUE for default catch-all [OK]

Common Mistakes:

• Using > instead of >= causing gaps
• Wrong order of conditions
• Missing TRUE default condition