IF
Choose between two answers.
Give one answer when a test holds and another when it does not
The second argument if the test is TRUE, the third if it is FALSE
Syntax
=IF(test, if_true, [if_false])
| Argument | Required | What goes here |
|---|---|---|
test | Required | A comparison, like B2>10 or C2="Pastry". |
if_true | Required | What to give back when the test holds. |
if_false | Optional | What to give back otherwise. Often the next IF, for a further band. |
How to use IF
IF takes a test, an answer for when it holds, and an answer for when it does not. The test is anything that comes out TRUE or FALSE, which in practice means a comparison: D2>30, C2="Overdue", E2>=1000.
Text answers go in quotation marks. Numbers and cell references do not. Leaving the third argument out gives FALSE back when the test fails, which is almost never what a schedule wants to show.
IF only ever works out the branch it lands on. That is what lets you put a division in one branch and a message about a zero in the other without the division ever running.
Nesting one IF inside another gives you bands, but past two or three levels it becomes hard to read and IFS says the same thing more plainly.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=IF(D2>30,"Chase","Leave")Twelve days is not more than thirty, so the FALSE branch answers. | Leave |
=IF(E4>1000,"Manager approval","Standard")The same shape applied to an amount rather than an age. | Manager approval |
=IF(D7>90,"Write-off review",IF(D7>60,"Escalate","Chase"))One IF inside another. The outer test is checked first, so the widest band has to come first or nothing ever reaches it. | Write-off review |
=IF(AND(D4>30,E4>500),"Chase","Leave")AND inside the test slot, which is how you ask for two conditions at once. | Chase |
Notes
- A missing third argument returns FALSE, not an empty cell. Write "" if you want the cell to look empty.
- IF returns whatever you put in the branch, so the same formula can hand back text in one case and a number in the other. A column mixing the two will not total.
Related
A reference tells you what it does. A unit makes you use it.