Excel Challenge 29 : Highlight Low Stock Items

What You Will Learn

Five products. Each has shelf stock in column B and warehouse stock in column C. If either location is too low, the product needs reordering. The threshold: shelf < 5 OR warehouse < 10.

OR checks multiple conditions and returns TRUE if any one of them passes. IF turns that into a readable label:

=IF(OR(B2<5, C2<10), "REORDER", "OK")

Breaking it down:

B2<5 — is shelf stock below 5? Wireless Mouse has 2 — TRUE.
C2<10 — is warehouse stock below 10? HDMI Cable has 5 — TRUE.
OR(...) — returns TRUE if either condition passes. An item with plenty of warehouse stock but only 1 unit on the shelf still needs attention.
IF(... ,"REORDER","OK") — shows REORDER if either threshold is breached.

The thresholds are different: shelf triggers at 5, warehouse triggers at 10. That is why OR is the right choice — each location has its own risk level. Shelf stock runs out faster (customers pick from it), so its threshold is lower. Warehouse stock has more buffer, so its threshold is higher to leave time for supplier orders.

In this dataset: Wireless Mouse (shelf=2), HDMI Cable (warehouse=5), Laptop Stand (both low), and Webcam HD (warehouse=0) all need reordering. USB-C Hub (12 shelf, 45 warehouse) is fine. That is 4 out of 5 products needing attention.

OR checks both stock locations. One low value triggers REORDER.

The audit formulas:

B9: =COUNTA(A2:A6) — total SKUs.
B10: =COUNTIF(D2:D6,"REORDER") — items needing reorder.

Alternative: MIN for a Single Threshold

If both locations used the same threshold (e.g., below 5), you could check the lowest value across both:

=IF(MIN(B2,C2)<5,"REORDER","OK")

MIN finds the smaller of the two numbers. If the weakest stock point is below 5, flag it. This is simpler but only works when both thresholds are the same.

Going Further: Multiple States

If you need more detail than REORDER/OK, use IFS for multiple levels:

=IFS(B2=0,"OUT",B2<5,"LOW",TRUE,"GOOD")

This returns "OUT" for zero stock, "LOW" for below 5, and "GOOD" for everything else.

OR is the right choice when each location has its own threshold. If both share the same threshold, MIN is simpler. Choose based on how your real inventory rules work.