IF, AND, OR and nested conditions

Many Excel test questions show a formula with IF and ask what it returns. On the job, IF is how a sheet decides: is this invoice overdue, does this order earn a discount, which band does this score fall in. The function itself is simple. Mistakes come from the order of the tests and from mixing up AND and OR.

The shape of IF

=IF(test, value_if_true, value_if_false). The test must be something that is either true or false, such as B2>100 or C2="Paid". Text in the test and in the results goes in double quotes; numbers do not.

Example 1: column B holds days overdue. =IF(B2>30,"Chase","OK") returns Chase when B2 is 45 and OK when B2 is 12. If B2 is exactly 30 it returns OK, because 30 is not greater than 30. Use >= if 30 should count.

Combining tests with AND and OR

AND is true only when every test is true. OR is true when at least one test is true. Both sit inside the IF test.

Example 2: a discount applies when quantity is at least 50 and the customer type is Trade. =IF(AND(B2>=50,C2="Trade"),0.1,0). For B2 = 60 and C2 = Trade the result is 0.1; for C2 = Retail it is 0.

Example 3: a row needs review if the amount is negative or the date is blank. =IF(OR(D2<0,E2=""),"Review","").

Read the question slowly. "Both conditions" means AND. "Either condition" means OR. Swapping them is the most common wrong answer.

Nested IF and the order of tests

A nested IF puts another IF in the false slot. Excel reads the tests from left to right and stops at the first true one, so put the tests in an order that cannot overlap.

Example 4: grade a score. =IF(B2>=80,"A",IF(B2>=60,"B","C")). For 85 the first test is true, so the result is A. For 72 the first test fails and the second passes, so B. For 40 both fail, so C. If you wrote B2>=60 first, a score of 85 would also pass that test and return B, which is wrong.

In newer versions, =IFS(B2>=80,"A",B2>=60,"B",TRUE,"C") does the same with less bracket counting. The final TRUE is the catch-all.

Common mistakes and a practice habit

• Forgetting quotes around text: =IF(C2=Paid,...) returns a #NAME? error.
• Comparing a number stored as text. "100" in quotes is not greater than 50. Convert the column first.
• Returning "0" in quotes. That is text, and SUM will ignore it.

Take any list you have, such as expenses or marks. Write three formulas: one plain IF, one with AND, one nested with three bands. Then type values that sit exactly on the boundaries and check the results against what you expect. Boundaries are where screening questions catch people.