Nested IF functions in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of Nested IF for Grade Calculation

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

A "F"

B "A"

C "B"

D "C"

💡 Hint

Think about the first condition that matches the value 85.

The formula checks if A1 is 90 or more, which is false for 85. Then it checks if A1 is 80 or more, which is true, so it returns "B".

❓ Function Choice

intermediate

Choosing the Correct Nested IF for Discount

Which formula correctly applies a discount based on purchase amount in cell B2: 20% if over 1000, 10% if over 500, otherwise no discount?

A =IF(B2>1000, B2*0.2, IF(B2>500, B2*0.1, B2))

B =IF(B2>1000, B2*0.8, IF(B2>500, B2*0.9, B2))

C =IF(B2>500, B2*0.9, IF(B2>1000, B2*0.8, B2))

D =IF(B2>1000, B2*0.8, IF(B2>500, B2*0.1, B2))

💡 Hint

Check the order of conditions and the calculation for discount.

Option B correctly checks the highest amount first and applies the correct discount percentages as multipliers.

📊 Formula Result

advanced

Result of Complex Nested IF with Text and Numbers

What is the result of the formula =IF(A1="Yes", IF(B1>10, "High", "Low"), "No") if A1 contains "Yes" and B1 contains 8?

A "Low"

B "High"

C "No"

D 0

💡 Hint

Check the first condition and then the nested condition carefully.

Since A1 is "Yes", the formula checks B1. B1 is 8 which is not greater than 10, so it returns "Low".

🎯 Scenario

advanced

Using Nested IF to Categorize Age Groups

You want to categorize ages in cell C1 as "Child" if under 13, "Teen" if 13 to 19, "Adult" if 20 to 64, and "Senior" if 65 or older. Which formula correctly does this?

A =IF(C1<13, "Child", IF(C1<20, "Teen", IF(C1<64, "Adult", "Senior")))

B =IF(C1<=13, "Child", IF(C1<19, "Teen", IF(C1<64, "Adult", "Senior")))

C =IF(C1<13, "Child", IF(C1<=19, "Teen", IF(C1<=64, "Adult", "Senior")))

D =IF(C1<13, "Child", IF(C1<20, "Teen", IF(C1<65, "Adult", "Senior")))

💡 Hint

Check the boundary values carefully for each age group.

Option D correctly uses less than comparisons to include the upper bounds properly: under 13, under 20, under 65, else senior.

❓ data_analysis

expert

Count How Many Cells Return "Pass" Using IF

You have a column D with scores. The formula =IF(D1>=50, "Pass", "Fail") is applied to each row. Which formula correctly counts how many "Pass" results are in the range E1:E100?

A =COUNTIF(E1:E100, "Pass")

B =COUNTIF(D1:D100, ">=50")

C =SUM(IF(D1:D100>=50, 1, 0))

D =COUNTIF(D1:D100, "Pass")

💡 Hint

Remember the formula output is "Pass" or "Fail" in a different column.

The IF formula outputs "Pass" or "Fail" in another column (e.g., E). Counting "Pass" in that output column E is done by COUNTIF(E1:E100, "Pass").

Practice

1. What does a nested IF function in Excel do?
=IF(A1>10, "High", IF(A1>5, "Medium", "Low"))

easy

A. It checks multiple conditions one after another and returns different results.

B. It adds numbers in multiple cells.

C. It sorts data in ascending order.

D. It creates a chart based on cell values.

Solution

1. Step 1: Understand the IF function

   The IF function checks a condition and returns one value if TRUE, another if FALSE.

2. Step 2: See how nesting works

   The nested IF checks the first condition; if FALSE, it checks the next condition inside another IF.

3. Final Answer:

   It checks multiple conditions one after another and returns different results. -> Option A

4. Quick Check:

   Nested IF = multiple condition checks [OK]

Hint: Think of nested IF as a decision tree [OK]

Common Mistakes:

• Confusing IF with SUM or other functions
• Thinking nested IF adds or sorts data
• Ignoring the order of conditions

2. Which of the following is the correct syntax for a nested IF function in Excel?

easy

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

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", ELSE IF(A1>5, "Medium", "Low"))

Solution

1. Step 1: Review correct IF syntax

   IF syntax requires parentheses enclosing condition, true result, and false result separated by commas.

2. Step 2: Check each option

   =IF(A1>10, "High", IF(A1>5, "Medium", "Low")) uses commas and proper parentheses; others use ELSE IF, semicolons, or missing parentheses which are incorrect.

3. Final Answer:

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

4. Quick Check:

   Correct IF syntax uses commas and parentheses [OK]

Hint: Remember: IF uses commas, not semicolons or ELSE [OK]

Common Mistakes:

• Using ELSE IF instead of nested IF
• Replacing commas with semicolons
• Missing parentheses around nested IF

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

medium

A. A

B. C

C. B

D. F

Solution

1. Step 1: Check first condition B2>90

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

2. Step 2: Check second condition B2>75

   80>75 is TRUE, so return "B" without checking further.

3. Final Answer:

   B -> Option C

4. Quick Check:

   80 is between 75 and 90, so grade = 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
• Picking 'C' or 'F' without checking all IFs

4. Identify the error in this nested IF formula:
=IF(A1>100, "High", IF(A1>50, "Medium", "Low"

medium

A. Extra comma after "Medium".

B. Using wrong comparison operator.

C. Incorrect use of quotation marks.

D. Missing closing parenthesis for the second IF function.

Solution

1. Step 1: Count parentheses

   The formula has one opening IF with two conditions but only one closing parenthesis at the end.

2. Step 2: Identify missing parenthesis

   The second IF function is missing its closing parenthesis, causing a syntax error.

3. Final Answer:

   Missing closing parenthesis for the second IF function. -> Option D

4. Quick Check:

   Every IF needs matching parentheses [OK]

Hint: Count parentheses pairs carefully [OK]

Common Mistakes:

• Assuming commas or quotes are wrong
• Not noticing missing parentheses
• Thinking comparison operators cause error

5. You want to assign a category in cell C2 based on sales in B2:
- "Excellent" if sales >= 1000
- "Good" if sales >= 500 but < 1000
- "Average" if sales >= 100 but < 500
- "Poor" if sales < 100
Which nested IF formula correctly assigns these categories?

hard

A. =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2>=100, "Average", "Poor")))

B. =IF(B2>=1000, "Excellent", IF(B2>=100, "Average", IF(B2>=500, "Good", "Poor")))

C. =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2<=100, "Poor", "Average")))

D. =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2>=100, "Poor", "Average")))

Solution

1. Step 1: Check conditions order

   Conditions must be checked from highest to lowest to avoid misclassification.

2. Step 2: Verify each nested IF matches category ranges

   =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2>=100, "Average", "Poor"))) checks >=1000, then >=500, then >=100, else "Poor" which matches the problem exactly.

3. Final Answer:

   =IF(B2>=1000, "Excellent", IF(B2>=500, "Good", IF(B2>=100, "Average", "Poor"))) -> Option A

4. Quick Check:

   Highest to lowest conditions in nested IF [OK]

Hint: Order conditions from largest to smallest [OK]

Common Mistakes:

• Checking smaller values before larger ones
• Mixing up category labels
• Using wrong comparison operators