IF and logic, where the boundary is the whole problem
IF takes three things: a test, what to return when the test is true, and what to return when it is false, as in =IF(B2>=50,"Pass","Fail"). The syntax is rarely the difficulty. The boundary is: >= includes the row sitting exactly on the threshold and > excludes it, and with a pass mark of 50 that is the difference between passing and failing a student who scored 50.
IF is where a spreadsheet starts making decisions instead of just calculating. It takes a test, an answer for when the test holds, and an answer for when it does not, and it applies that to every row without you reading any of them.
Nearly every IF that goes wrong goes wrong in one of two places, and neither is the syntax. Either the comparison is off by one row at the boundary, or there are several tests and they are in an order that means some of them never run.
Three arguments, one decision
An IF formula reads as a sentence: if this is true, give me that, otherwise give me the other. The three parts are separated by commas and the two answers can be text in quotes, a number, a cell reference, or another formula.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Ada | 72 | Pass |
| 3 | Blake | 50 | Pass |
| 4 | Chen | 49 | Fail |
| 5 | Dara | 88 | Pass |
| 6 | Eli | 31 | Fail |
=IF(B3>=50,"Pass","Fail")Blake's row. Change the >= to > and this single cell flips, and nothing else on the sheet does.
AND when every condition matters, OR when any will do
AND and OR go inside the test. AND returns true only when everything inside it is true; OR returns true when at least one thing is. Both take as many conditions as you like, separated by commas.
=IF(AND(B2>=50,B5>=50),"Both passed","Not both")AND: every condition has to hold. One failure is enough to send the whole thing to the false branch.
=IF(OR(B4>=50,B6>=50),"At least one","Neither")OR: one is enough. Both of these scores are under 50, so the test fails.
The word people reach for in English is a poor guide here. A bonus paid when sales are high and returns are low is an AND. A review triggered when an order is late or expensive is an OR. Read the rule back to yourself asking whether one condition on its own should be enough, because that is the only question these two differ on.
Nested IF, and why the order is the exercise
More than two outcomes means an IF inside the false branch of another IF. Excel stops at the first test that passes, so the tests have to be written in an order where passing one genuinely means the answer is settled.
=IF(B5>=80,"A",IF(B5>=60,"B",IF(B5>=50,"C","D")))Highest band first. Dara scored 88, the first test passes, and nothing after it is evaluated.
Write those same bands from the bottom up and every score above 50 passes the first test, so every student gets a C and the other branches are unreachable. The formula is syntactically perfect, returns an answer for every row, and is wrong for almost all of them.
What goes wrong
These are the mistakes this part exists to prevent. Each one is reasonable, which is why people make it and why nothing on the sheet objects.
Using > where >= was meant
WhyIn conversation, over fifty and fifty or more are used interchangeably, and on most datasets no row sits exactly on the line to reveal the difference.
InsteadDecide explicitly what happens to a row on the threshold, then write the comparison that produces it, and keep a boundary row in your test data.
Writing nested bands from the bottom up
WhyStarting at the lowest grade feels like working through the list in order, and the formula runs without error.
InsteadOrder the tests from the most selective to the least. Check a value that should land in the top band: if it comes out in the bottom one, the chain is reversed.
Reaching for OR when the rule says and
WhyEnglish uses and for listing conditions even when only one needs to hold, so the wording of the rule is an unreliable guide.
InsteadAsk whether one condition on its own should be enough to trigger the result. If yes it is OR; if every one is required it is AND.
Common questions
How do I write an IF statement in Excel?
=IF(test, value_if_true, value_if_false). For example =IF(B2>=50,"Pass","Fail") returns Pass for any score of 50 or more. Text answers go in double quotes; numbers and references do not.
How do I use multiple IF statements in Excel?
Put the next IF inside the false branch of the previous one, testing from the most selective condition down. IFS does the same job as a flat list of pairs, which is easier to read past about three bands.
What is the difference between AND and OR in Excel?
AND is true only when every condition inside it is true. OR is true when at least one is. Both go inside the test argument of an IF and both accept several conditions.
Why does my nested IF always return the same answer?
The tests are almost certainly in the wrong order. Excel stops at the first one that passes, so if the least selective band is tested first, every row matches it and the later branches never run.
How many IFs can you nest in Excel?
Excel allows 64 levels, but anything past three or four is painful to read and to debug. Use IFS, or a small lookup table with VLOOKUP set to approximate match, which is what banded lookups exist for.