Nested IF functions in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This data shows a student's score in cell A2. We want to assign a grade based on the score using nested IF functions.

Cell	Value
A1	Score
A2	85
B1	Grade

Formula Trace

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "F")))

Step 1: A2>=90

Step 2: 85>=90

Step 3: IF(A2>=80, "B", IF(A2>=70, "C", "F"))

Step 4: 85>=80

Step 5: "B"

Cell Reference Map

   A    B
1 Score Grade
2  85   -->

The formula references cell A2 which contains the score 85. The result is placed in B2.

Result

   A    B
1 Score Grade
2  85    B

The formula evaluates the score 85 and assigns grade B in cell B2.

Sheet Trace Quiz - 3 Questions

What grade will the formula assign if the score in A2 is 92?

A B

B A

C C

D F

Key Result

Nested IF functions check conditions in order and return the first true condition's value, else the last value.

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