IF in Excel: flag low stock and keep missing counts visible

Write the rule in plain language

A workshop checks its spare-parts shelf. Its rule is to flag an item when the counted quantity is below the minimum. At the minimum or above it, no reorder flag is needed. Put the item name in A2, counted quantity in B2 and minimum level in C2.

Use Cable clips, 8 and 8 as the first row. The minimum is a numeric setting supplied by the workshop; this lesson does not calculate an optimal stock level.

Choose the two results

The test B2<C2 asks whether the count is below the minimum. IF returns Reorder when that test is true and Enough when it is false. Text results are enclosed in quotation marks; the cell references and comparison operator are not.

=IF(B2<C2,"Reorder","Enough")

Equality is part of the rule

With 8 counted and a minimum of 8, the result is Enough. If the rule instead said at or below the minimum, the operator would be <= and this same row would return Reorder. Neither operator is inherently better: it must match the intended rule.

Check the three neighbouring counts: 7 should return Reorder, 8 should return Enough, and 9 should return Enough. These cases are more revealing than testing only a count far below the minimum.

Do not treat an uncounted shelf as an empty shelf

A real count of zero means no clips were found. An empty input means the count has not been entered. Those are different operational situations, even if a simple comparison happens to treat a blank like zero.

Use ISNUMBER to require a numeric count before applying the threshold rule. An empty cell or a text note returns Enter count. A numeric zero still reaches the threshold comparison and returns Reorder.

=IF(ISNUMBER(B2),IF(B2<C2,"Reorder","Enough"),"Enter count")

Check imported counts before relying on the labels

A value stored as text is not the same as a numeric count. If a supplier file imports the characters 8 as text, this guarded formula asks for a count rather than silently accepting it. Convert and validate the source value deliberately.

ISNUMBER is only a type check. A negative number or fractional quantity is still a number, so it is not complete validation for a shelf of individual parts. Reject those inputs separately when the business rule requires non-negative whole counts. The minimum in C2 must also be a valid number.

Copy the formula with the right references

If each item has its own count and minimum, enter the formula in D2 and copy it down. Relative references B2 and C2 then become B3 and C3 for the next item.

If all rows share a single minimum stored in F2 instead, lock that reference as $F$2. Do not lock the stock-count reference unless every output is intentionally checking the same item.

Try the missing-value branch

In the practice table, first change B2 from 8 to 7 and calculate. Then replace the formula with the guarded version and clear B2: the result should be Enter count. Enter 0 to get Reorder. This checks that the formula distinguishes missing work from a completed count of an empty shelf.

These labels describe the result of a spreadsheet rule. They do not place an order, reserve inventory or update another system.

Further reading

• Microsoft: IF function
• Microsoft: comparison operators