IF function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of nested IF formula

What is the output of the formula =IF(A1>10, IF(A1<20, "Medium", "High"), "Low") when cell A1 contains the value 15?

A "Medium"

B 15

C "High"

D "Low"

💡 Hint

Think about the first condition and then the second inside the nested IF.

The formula first checks if A1 is greater than 10, which is true for 15. Then it checks if A1 is less than 20, which is also true. So it returns "Medium".

❓ Function Choice

intermediate

Choosing the correct IF formula for grading

You want to assign grades based on scores in cell B2: "Pass" if score is 50 or more, otherwise "Fail". Which formula correctly does this?

A =IF(B2>50, "Pass", "Fail")

B =IF(B2>=50, "Pass", "Fail")

C =IF(B2<50, "Fail", "Pass")

D =IF(B2=50, "Pass", "Fail")

💡 Hint

Remember the condition includes scores equal to 50.

Option B uses >= which includes 50 and above as Pass. Option B excludes 50, C reverses logic, D only passes exactly 50.

📊 Formula Result

advanced

Result of IF with AND function

What is the result of =IF(AND(C1>0, C1<=100), "Valid", "Invalid") if C1 contains -5?

A FALSE

B "Valid"

C TRUE

D "Invalid"

💡 Hint

AND requires all conditions to be true.

Since C1 is -5, the first condition C1>0 is false, so AND returns FALSE and IF returns "Invalid".

🎯 Scenario

advanced

Using IF to calculate bonus

You want to calculate a bonus in cell D2: if sales in C2 are above 1000, bonus is 10% of sales, otherwise 0. Which formula should you use?

A =IF(C2>1000, 0, C2*0.1)

B =IF(C2>=1000, C2*0.1, 0)

C =IF(C2>1000, C2*0.1, 0)

D =IF(C2<1000, C2*0.1, 0)

💡 Hint

Bonus applies only if sales are strictly greater than 1000.

Option C correctly applies 10% bonus if sales are above 1000, else 0. Option C includes 1000 which is not required. Options C and D reverse logic.

❓ data_analysis

expert

Count how many cells meet IF condition

You have a list of numbers in cells A1:A10. Which formula counts how many numbers are greater than 50 using IF inside SUM?

A =SUM(IF(A1:A10>50, 1, 0))

B =SUM(IF(A1:A10>50))

C =SUM(A1:A10>50)

D =COUNTIF(A1:A10, ">50")

💡 Hint

IF returns arrays; SUM adds them up.

Option A uses IF to return 1 for each cell >50 and 0 otherwise, then sums them. Option A is incomplete syntax. Option A is correct but does not use IF inside SUM. Option A sums TRUE/FALSE as numbers but is less explicit.