IFS function in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to return "Pass" if the score is 50 or more, otherwise "Fail".

Google Sheets

=IFS(A1 >= 50, [1], TRUE, "Fail")

Drag options to blanks, or click blank then click option'

A"Pass"

B"Fail"

CTRUE

D50

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around text like Pass

Using a number instead of text for the result

The IFS function checks conditions in order. If the score in A1 is 50 or more, it returns "Pass".

2fill in blank

medium

Complete the code to return "Excellent" if score is 90 or more, "Good" if 70 or more, otherwise "Needs Improvement".

Google Sheets

=IFS(A1 >= 90, "Excellent", A1 >= 70, [1], TRUE, "Needs Improvement")

Drag options to blanks, or click blank then click option'

A"Needs Improvement"

B"Good"

CTRUE

D70

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the order of conditions

Not using quotes for text results

The second condition returns "Good" if the score is 70 or more but less than 90.

3fill in blank

hard

Fix the error in the formula to return "Low" if value is less than 50, "Medium" if less than 80, else "High".

Google Sheets

=IFS(A1 < 50, "Low", A1 < 80, [1], TRUE, "High")

Drag options to blanks, or click blank then click option'

A"Medium"

BA1 < 80

C"Low"

D"High"

Attempts:

3 left

💡 Hint

Common Mistakes

Using a condition instead of a result

Repeating the same result for different conditions

The second condition's result should be "Medium" for values less than 80 but not less than 50.

4fill in blank

hard

Fill both blanks to create a formula that returns "Fail" if score is below 50, "Pass" if below 75, else "Excellent".

Google Sheets

=IFS(A1 < [1], "Fail", A1 < [2], "Pass", TRUE, "Excellent")

Drag options to blanks, or click blank then click option'

A50

B75

C80

D60

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the two numbers

Using numbers outside the score range

The first blank is 50 for failing scores below 50. The second blank is 75 for passing scores below 75.

5fill in blank

hard

Fill all three blanks to create a formula that returns "Poor" if score is below 40, "Average" if below 70, "Good" if below 90, else "Excellent".

Google Sheets

=IFS(A1 < [1], "Poor", A1 < [2], "Average", A1 < [3], "Good", TRUE, "Excellent")

Drag options to blanks, or click blank then click option'

A40

B70

C90

D80

Attempts:

3 left

💡 Hint

Common Mistakes

Using thresholds in wrong order

Repeating the same number for multiple blanks

The blanks represent the upper limits for each category: 40 for Poor, 70 for Average, and 90 for Good.

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