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").