Nested IF functions in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to check if a score in cell A1 is greater than 50.

Google Sheets

=IF(A1 [1] 50, "Pass", "Fail")

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

A>

B=

C<

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' will reverse the logic.

Using '=' will only check if the score equals 50.

The formula checks if the value in A1 is greater than 50. If true, it returns "Pass", otherwise "Fail".

2fill in blank

medium

Complete the nested IF formula to assign grades: if score in B2 is greater than 80, return "A", else "B".

Google Sheets

=IF(B2 [1] 80, "A", "B")

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

A<=

B>

C<

D>=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<=' will give wrong grade assignments.

Using '>=' includes 80, but the task says strictly greater than.

The formula checks if the score in B2 is greater than 80. If yes, it returns "A", otherwise "B".

3fill in blank

hard

Fix the error in this nested IF formula to assign "High", "Medium", or "Low" based on value in C3.

Google Sheets

=IF(C3 > 70, "High", IF(C3 [1] 40, "Medium", "Low"))

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

A>

B<

C>=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' or '<=' in the second IF causes wrong logic.

Using '>' excludes 40, which may be incorrect.

The second IF checks if C3 is greater than or equal to 40 to assign "Medium"; otherwise, it assigns "Low".

4fill in blank

hard

Fill both blanks to complete the nested IF formula that assigns "Excellent", "Good", or "Needs Improvement" based on D4.

Google Sheets

=IF(D4 [1] 90, "Excellent", IF(D4 [2] 60, "Good", "Needs Improvement"))

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

A>=

B<

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>=' excludes 90 from "Excellent".

Using '<=' instead of '>' includes 60 in "Needs Improvement".

The first condition checks if D4 is greater than or equal to 90 for "Excellent". The second checks if D4 is greater than 60 for "Good"; otherwise, "Needs Improvement".

5fill in blank

hard

Fill all three blanks to complete the nested IF formula that assigns "Pass", "Retake", or "Fail" based on E5.

Google Sheets

=IF(E5 [1] 70, "Pass", IF(E5 [2] 50, "Retake", IF(E5 [3] 30, "Fail", "Check")))

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

A>=

B<

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>=' in the second blank excludes 50 from "Retake".

Using '>=' instead of '<' in the third blank causes wrong logic.

The formula checks if E5 is greater than or equal to 70 for "Pass". Then if E5 is greater than or equal to 50 for "Retake". Then if E5 is less than 30 for "Fail". Otherwise, it returns "Check".

Practice

1. What does a nested IF function do in Google Sheets?

easy

A. It creates charts automatically.

B. It adds numbers in a list.

C. It checks multiple conditions one after another.

D. It sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of IF function

   The IF function checks one condition and returns a value if true or another if false.

2. Step 2: Understand nested IF

   Nested IF means putting one IF inside another to check multiple conditions step by step.

3. Final Answer:

   It checks multiple conditions one after another. -> Option C

4. Quick Check:

   Nested IF = multiple condition checks [OK]

Hint: Nested IF checks conditions stepwise, like a decision tree [OK]

Common Mistakes:

• Thinking nested IF adds or sorts data
• Confusing IF with other functions like SUM or SORT
• Believing nested IF creates charts

2. Which of the following is the correct syntax for a nested IF in Google Sheets to check if A1 is greater than 10, then greater than 5, else "Low"?

easy

A. =IF(A1>10, IF(A1>5, "Medium", "Low"), "High")

B. =IF(A1>10, "High", IF(A1>5, "Medium", "Low"))

C. =IF(A1>10, "High", IF(A1<5, "Medium", "Low"))

D. =IF(A1>10, "High", IF(A1>5, "Low", "Medium"))

Solution

1. Step 1: Check the first condition

   The formula first checks if A1 is greater than 10, returning "High" if true.

2. Step 2: Check the nested condition

   If the first is false, it checks if A1 is greater than 5, returning "Medium" if true, else "Low".

3. Final Answer:

   =IF(A1>10, "High", IF(A1>5, "Medium", "Low")) -> Option B

4. Quick Check:

   Correct nested IF syntax = =IF(A1>10, "High", IF(A1>5, "Medium", "Low")) [OK]

Hint: Put second IF inside the false part of first IF [OK]

Common Mistakes:

• Swapping true and false parts
• Using wrong comparison operators
• Misplacing parentheses

3. Given the formula =IF(B2>90, "A", IF(B2>75, "B", IF(B2>60, "C", "F"))) and B2 = 80, what will be the output?

medium

A. "B"

B. "C"

C. "A"

D. "F"

Solution

1. Step 1: Check if B2 > 90

   Since B2 is 80, 80 > 90 is false, so move to next IF.

2. Step 2: Check if B2 > 75

   80 > 75 is true, so the formula returns "B".

3. Final Answer:

   "B" -> Option A

4. Quick Check:

   80 > 75 = "B" [OK]

Hint: Check conditions top-down; first true stops checking [OK]

Common Mistakes:

• Choosing "A" because 80 is close to 90
• Ignoring order of conditions
• Confusing output labels

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

medium

A. Wrong use of quotes around text

B. Incorrect comparison operators

C. Too many IF functions nested

D. Missing value if all conditions are false

Solution

1. Step 1: Analyze the nested IF structure

   The last IF checks if A1<30 and returns "Fail" if true, but no value is given if false.

2. Step 2: Identify missing else value

   If none of the conditions are true (for example, A1=30), the formula returns FALSE because the last IF lacks a false value.

3. Final Answer:

   Missing value if all conditions are false -> Option D

4. Quick Check:

   Last IF missing false part = Missing value if all conditions are false [OK]

Hint: Always provide value for false in last IF [OK]

Common Mistakes:

• Leaving out false value in last IF
• Assuming IF auto-returns something
• Misreading comparison logic

5. You want to assign a label in cell C2 based on score in B2: "Excellent" if >90, "Good" if >75, "Average" if >50, "Poor" otherwise. Which formula correctly uses nested IF to do this?

hard

A. =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2>50, "Average", "Poor")))

B. =IF(B2>50, "Average", IF(B2>75, "Good", IF(B2>90, "Excellent", "Poor")))

C. =IF(B2>90, "Excellent", IF(B2>50, "Average", IF(B2>75, "Good", "Poor")))

D. =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2<50, "Poor", "Average")))

Solution

1. Step 1: Order conditions from highest to lowest

   Check if B2 > 90 first, then > 75, then > 50, else "Poor".

2. Step 2: Verify nested IF matches order

   =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2>50, "Average", "Poor"))) correctly nests IFs in descending order of score thresholds with correct labels.

3. Final Answer:

   =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2>50, "Average", "Poor"))) -> Option A

4. Quick Check:

   Descending order nested IF = =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2>50, "Average", "Poor"))) [OK]

Hint: Check highest condition first, nest lower next [OK]

Common Mistakes:

• Ordering conditions incorrectly
• Mixing up labels with wrong conditions
• Using < instead of > in conditions