IF function in Google Sheets - 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 "Low"

C "High"

D 15

💡 Hint

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

The formula first checks if A1 is greater than 10. Since 15 > 10 is TRUE, it evaluates the nested IF. The nested IF checks if A1 is less than 20. Since 15 < 20 is TRUE, it returns "Medium".

❓ Function Choice

intermediate

Choose 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&gt;50, "Pass", "Fail")

C =IF(B2&lt;50, "Fail", "Pass")

D =IF(B2&gt;=50, "Pass", "Fail")

💡 Hint

Remember the condition includes scores equal to 50.

Option D uses >=50 which means 50 or more is "Pass". Option D excludes 50, option D reverses logic, and option D only passes if exactly 50.

📊 Formula Result

advanced

Result of IF with AND condition

Given the formula =IF(AND(A1>0, B1<5), "Yes", "No"), what is the result if A1=3 and B1=7?

A "No"

B "Yes"

C TRUE

D FALSE

💡 Hint

Both conditions inside AND must be true for the IF to return "Yes".

A1>0 is TRUE but B1<5 is FALSE (7<5 is false). AND returns FALSE, so IF returns "No".

🎯 Scenario

advanced

Using IF to categorize sales

You have sales amounts in cell C2. You want to categorize sales as "Low" if less than 100, "Medium" if between 100 and 500 (inclusive), and "High" if above 500. Which formula correctly categorizes the sales?

A =IF(C2&lt;100, "Low", IF(C2&gt;=100, IF(C2&lt;=500, "Medium", "High"), "High"))

B =IF(C2&lt;100, "Low", IF(C2&gt;=100, IF(C2&gt;500, "High", "Medium"), "Medium"))

C =IF(C2&lt;100, "Low", IF(C2&lt;=500, "Medium", "High"))

D =IF(C2&lt;100, "Low", IF(C2&gt;=100, IF(C2&lt;=500, "Medium", "High")))

💡 Hint

Think about the order of conditions and how IF evaluates them.

Option C correctly checks if C2<100 for "Low", else if C2<=500 for "Medium", else "High". Other options have redundant or incorrect nested IFs.

❓ data_analysis

expert

Count how many cells meet IF condition

You have a list of numbers in cells A1:A10. You want to count how many numbers are greater than 50 using an IF formula inside another function. Which formula gives the correct count?

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

B =SUM(IF(A1:A10&gt;50, 1, 0))

C =IF(SUM(A1:A10&gt;50), COUNT(A1:A10), 0)

D =SUMPRODUCT(IF(A1:A10&gt;50, 1))

💡 Hint

Think about how IF returns arrays and how SUM can add them up.

Option B uses IF to return 1 for each cell >50 and 0 otherwise, then sums them. Option B is simpler but does not use IF. Option B is incorrect logic. Option B is invalid because IF inside SUMPRODUCT is not array-entered.