Nested IF functions in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of Nested IF for Grade Evaluation

Given the formula =IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F"))) in cell B1, what will be the output if A1 contains the value 85?

A "A"

B "C"

C "B"

D "F"

💡 Hint

Think about the conditions checked in order from highest to lowest.

The formula checks if A1 is 90 or more for "A". Since 85 is less than 90 but greater than or equal to 80, it returns "B".

📊 Formula Result

intermediate

Nested IF Output for Discount Calculation

What is the result of the formula =IF(B2>1000, "20%", IF(B2>500, "10%", "0%")) if cell B2 contains 500?

A "0%"

B "20%"

C "10%"

D 0

💡 Hint

Check each condition carefully and remember the order matters.

Since 500 is not greater than 1000 and not greater than 500 (it's equal), both conditions fail, so the formula returns "0%".

❓ Function Choice

advanced

Choosing the Correct Nested IF for Age Group

Which formula correctly assigns age groups as follows: "Child" if age < 13, "Teen" if age is between 13 and 19 inclusive, and "Adult" if age is 20 or more? Assume age is in cell A1.

A =IF(A1<=12, "Child", IF(A1<=19, "Teen", "Adult"))

B =IF(A1<13, "Child", IF(A1<=19, "Teen", "Adult"))

C =IF(A1<=13, "Child", IF(A1<19, "Teen", "Adult"))

D =IF(A1<13, "Child", IF(A1<19, "Teen", "Adult"))

💡 Hint

Check the boundary conditions carefully for each age group.

Option B correctly uses <13 for Child, <=19 for Teen (which includes 19), and Adult otherwise. Other options misplace boundaries.

🎯 Scenario

advanced

Nested IF for Multi-level Commission Rates

You want to calculate commission based on sales in cell C1: 5% if sales < 1000, 10% if sales between 1000 and 4999, 15% if sales between 5000 and 9999, and 20% if sales 10000 or more. Which formula correctly calculates the commission rate as a decimal (e.g., 0.05 for 5%)?

A =IF(C1<1000, 0.05, IF(C1<5000, 0.10, IF(C1<10000, 0.15, 0.20)))

B =IF(C1<=1000, 0.05, IF(C1<=5000, 0.10, IF(C1<=10000, 0.15, 0.20)))

C =IF(C1<1000, 0.05, IF(C1<=4999, 0.10, IF(C1<=9999, 0.15, 0.20)))

D =IF(C1<=999, 0.05, IF(C1<=4999, 0.10, IF(C1<=9999, 0.15, 0.20)))

💡 Hint

Remember to use less than for upper bounds to avoid overlap.

Option A correctly uses < for upper bounds to avoid overlapping ranges and matches the problem's ranges exactly.

❓ data_analysis

expert

Count of Cells Returning "Pass" in Nested IF Formula

You have a column A with scores from 0 to 100. The formula =IF(A1>=60, "Pass", "Fail") is applied down column B for 100 rows. How many cells in column B will show "Pass" if the scores in column A are all integers from 0 to 99?

A 61

B 39

C 60

D 40

💡 Hint

Count how many numbers from 0 to 99 are 60 or greater.

Scores from 60 to 99 inclusive are 40 numbers, but since 0 to 99 is 100 numbers total, count is 40. However, 60 to 99 is 40 numbers, so answer is 40.

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