Day 3 — IF and logical tests

Making a formula decide for itself. This is where a spreadsheet stops adding up and starts thinking.

A logical test is a question with a yes or no answer

Every formula on this page runs against the Sales sheet below. The row numbers and column letters are real — F2 really is 165000.

Every one of those returns TRUE or FALSE. On their own they are not very useful. Feed them to IF and they become decisions.

= equals <> does not equal > greater than >= greater than or equal < less than <= less than or equal

IF

=IF(test, value if TRUE, value if FALSE)

Read it as a sentence: if the amount is over 100000, write "Large", otherwise write "Small".

IF can return a calculation, not just a label

Order 1004 has 10 keyboards, so it qualifies for the 10% discount: 12000 becomes 10800. Order 1001 has only 3 laptops, so it is left alone.

AND and OR

When one condition is not enough. AND needs every test to pass; OR needs only one.

The shape people forget AND and OR go inside IF as the test: =IF(AND(...),"yes","no"). They are not a replacement for IF, they are an ingredient.

Nested IF — a decision with more than two outcomes

Put another IF where the FALSE result goes. Excel keeps asking until one test passes.

Order matters enormously The tests are checked top to bottom and the first TRUE wins. If you put F2>50000 first, every large order would be labelled "C" and the other tests would never run. Always go from the most extreme condition to the least.

IFS — the same thing, readable

=IFS(test1, result1, test2, result2, ...)

Same answers, far easier to read and to fix. The final TRUE acts as "anything else" — without it, a value matching no test returns #N/A.

IFERROR — catching a broken formula

=IFERROR(formula, what to show if it breaks)

Use it deliberately, not everywhere IFERROR hides the error, it does not fix it. Wrapping every formula in IFERROR is how a broken report looks perfectly healthy for six months. Use it where you expect a specific failure — a blank divisor, a lookup that legitimately finds nothing.

Counting TRUEs

A logical test returns TRUE or FALSE, and Excel treats TRUE as 1 in arithmetic. That makes counting easy:

The last one counts rows where both are true — multiplying two TRUE/FALSE lists gives 1 only where both are 1. Tomorrow you will meet COUNTIFS, which does the same job with far less typing.

Try these yourself

Label order 1009 as "Large" or "Small" using a 150000 threshold.
Write a test that is TRUE only when the region is South and the quantity is above 5.
Build a nested IF that grades quantity: 10 or more is "Bulk", 5 or more is "Medium", otherwise "Small".
Rewrite that grade using IFS.
Use IFERROR to show "n/a" instead of an error when dividing F2 by zero.

← PreviousDay 2 — SUM, AVERAGE, COUNT and friends Next →Day 4 — COUNTIF, SUMIF and the IFS family