IF: make the rule clear before you write the formula
The hardest part of an IF formula is often deciding what should count as true. Is a stock level of exactly ten acceptable, or does it trigger a reorder? Put that rule into words first; the formula is much easier to check afterward.
Formula syntax
=IF(logical_test, value_if_true, [value_if_false])
What goes into it
logical_test
Condition that evaluates to TRUE or FALSE.
value_if_true
Value or expression returned when the test is TRUE.
value_if_false
Optional fallback when the test is FALSE.
A quick example first
=IF(82>=70,"Pass","Review")
Returns “Pass” because 82 is at least 70.
=IF(C2="","Missing",C2)
Labels a blank C2 without changing nonblank values.
Decide whether an order needs review
Enter quantities in A2:A5 and minimum stock in B2:B5. A value equal to the minimum is acceptable under this rule; a missing quantity must be flagged separately.
| Row | A: Stock | B: Minimum |
|---|---|---|
| 2 | 12 | 10 |
| 3 | 10 | 10 |
| 4 | 7 | 10 |
| 5 | (leave empty) | 10 |
| Formula | Expected result / interpretation |
|---|---|
| =IF(A2=””,”Missing stock”,IF(A2<B2,”Reorder”,”OK”)) | OK; fill down for OK, Reorder, Missing stock |
| =IF(A3<=B3,”Reorder”,”OK”) | Reorder — a different rule at the boundary |
The outer IF checks for missing input before the numeric comparison. This avoids treating a blank as a zero-stock observation. The inner IF uses < rather than <= because stock at the threshold is permitted. Neither operator is inherently better: the business rule decides which is correct.
Before you copy this into a real sheet
Try a quantity of zero, a blank cell, a formula returning an empty string, and a text entry such as unknown. This example detects blanks but does not validate every nonnumeric entry; add an ISNUMBER check or data validation where quantities are typed manually. Avoid replacing a missing quantity with zero unless that is the intended meaning. Keep the minimum in a cell so a policy change does not require editing every formula.
Useful habits
- Keep the logical test simple enough to audit separately.
- Use AND or OR when several conditions belong to the same decision.
- For many sequential conditions, IFS or a lookup table can be easier to maintain.
Where people get caught out
- Deeply nesting IF statements until the decision path is hard to trace.
- Comparing numbers stored as text and assuming the comparison behaves like numeric data.
Other functions worth a look
A detail worth remembering
Test the exact boundary, not just an obviously high and obviously low value. One character in the comparison operator can change who gets included.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.