Why logical functions enable decision-making in Excel - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of nested IF formula

What is the result of the formula =IF(A1>10, IF(B1<5, "Low", "High"), "Medium") when A1=12 and B1=3?

A "Medium"

B "High"

C "Low"

D 0

💡 Hint

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

Since A1 is 12 (greater than 10), the formula checks the nested IF. B1 is 3 (less than 5), so it returns "Low".

❓ Function Choice

intermediate

Choosing the correct logical function

Which logical function would you use to check if both conditions A1>5 and B1<10 are true?

A AND(A1>5, B1<10)

B OR(A1>5, B1<10)

C NOT(A1>5, B1<10)

D IF(A1>5, B1<10)

💡 Hint

Think about a function that requires all conditions to be true.

AND returns TRUE only if all conditions are true. OR returns TRUE if any condition is true. NOT negates a single condition. IF is for conditional output, not logical checks.

🎯 Scenario

advanced

Using logical functions for grading

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

A =IF(A1<50, "Pass", "Fail")

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

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

D =IF(A1>50, "Pass", "Fail")

💡 Hint

Remember the condition includes 50 as passing.

Option B uses >= which includes 50 as passing. Option B excludes 50. Option B reverses the logic. Option B is incorrect logic.

📊 Formula Result

advanced

Result of combined AND and OR

What is the result of =IF(AND(A1>5, OR(B1=10, C1=20)), "Yes", "No") when A1=6, B1=9, and C1=20?

A "Yes"

B "No"

C 0

D Error

💡 Hint

Check the AND condition and inside it the OR condition carefully.

A1>5 is TRUE (6>5). OR(B1=10, C1=20) is TRUE because C1=20. AND(TRUE, TRUE) is TRUE, so IF returns "Yes".

❓ data_analysis

expert

Count how many cells meet multiple conditions

You have scores in cells A1:A5. Which formula counts how many scores are greater than 50 and less than 80?

A =COUNTIFS(A1:A5, ">50<80")

B =COUNTIF(A1:A5, ">50<80")

C =COUNTIF(A1:A5, ">50") + COUNTIF(A1:A5, "<80")

D =COUNTIFS(A1:A5, ">50", A1:A5, "<80")

💡 Hint

Use COUNTIFS to apply multiple conditions on the same range.

COUNTIFS counts cells meeting all conditions. Options A and B have invalid criteria syntax. Option D counts separately and sums, giving wrong count.