Logical
IF
Definition
IF returns one value when a test is true and a different value when the test is false.
Syntax
=IF(logical_test, value_if_true, [value_if_false])
Arguments
| Argument | Meaning |
|---|---|
logical_test |
A comparison that is either true or false, such as B2<10. |
value_if_true |
What the cell shows when the test is true. Text must be wrapped in double quotes. |
value_if_false |
Optional. What the cell shows when the test is false. |
Worked example
A shop flags items that are running low. Units in stock are in B2:B4, and anything below 10 needs a reorder.
| A: Item | B: Units in stock |
|---|---|
| Mugs | 8 |
| Plates | 31 |
| Bowls | 26 |
Formula: =IF(B2<10,"Reorder","OK")
Result: Reorder. Mugs have 8 units, which is below 10, so the first text is returned. Filled down, the formula gives Reorder, OK, and OK.
Practice
Cell B2 holds an exam mark of 52. Write a formula that shows "Pass" when the mark is at least 60 and "Retry" otherwise.
- In C2, write an IF formula that shows Pass when B2 is at least 60 and Retry otherwise.
- C2 should show the result for 52.
Related functions
IF often wraps other functions, for example testing whether SUM of a range passes a target or whether COUNT reaches a minimum.
Common mistake
Leaving the text results without quotes, as in =IF(B2<10,Reorder,OK), makes Excel read them as names and return #NAME?.