Logical & Conditional
Flag & Count Values NOT Between X and Y (OR & Two-Tail COUNTIF)
Flag and count numeric measurements strictly outside a chosen inclusive range, even when the two bounds are entered in reverse order.
Catch both low-end and high-end spec violations even if X and Y are entered backwards
Quality-control engineers, pharmacy cold-chain monitors (2.0°C to 8.0°C vaccine fridges), and financial audit rules constantly test whether a measurement falls NOT between X and Y—meaning strictly below the lower limit OR strictly above the upper limit. The thresholds in this example are illustrative; an in-range reading does not establish product safety, and the formula does not evaluate how long a temperature excursion lasted. For vaccines, follow the manufacturer and local protocol, including immediate action on excursions; do not infer usability from this example.
Writing OR(B2 < MIN($E$2, $E$3), B2 > MAX($E$2, $E$3)) improves on naive OR(B2 < $E$2, B2 > $E$3) in one huge practical way: wrapping the bounds in MIN and MAX makes the rule immune to swapped user inputs (if someone types 8.0 in E2 and 2.0 in E3, MIN = 2.0 and MAX = 8.0 still work seamlessly). Pair it with COUNTIF(B2:B7, "<" & MIN(E2, E3)) + COUNTIF(B2:B7, ">" & MAX(E2, E3)) to tally every low and high excursion across the batch.
| Vaccine Fridge Probe (°C, Safe Window [2.0, 8.0]) | Out-of-Bounds Test OR(< Min, > Max) | Excursion Direction |
|---|---|---|
| 1.4 °C (Sub-Threshold Freeze Risk) | TRUE | Below Lower Spec (< 2.0 °C) |
| 2.0 °C and 5.6 °C (Boundary & Nominal) | FALSE | Within Example Range [2.0, 8.0] |
| 8.9 °C and 9.5 °C (Door-Open Warm Spike) | TRUE | Above Upper Spec (> 8.0 °C) -> 3 Total Excursions |
Swap-Safe Row Flag for Value Strictly Outside [X, Y]
Returns TRUE when B2 is strictly below MIN($E$2, $E$3) or strictly above MAX($E$2, $E$3), and FALSE on exact boundaries.
=IF(COUNT(B2, $E$2, $E$3) < 3, "Missing numeric input", OR(B2 < MIN($E$2, $E$3), B2 > MAX($E$2, $E$3)))Count Total Two-Sided Out-of-Bounds Readings Across Column B2:B7
Counts only numeric readings outside the two numeric limits. Check missing readings separately; this is not a determination of vaccine safety.
=IF(COUNT(E2:E3) <> 2, "Missing numeric limits", SUMPRODUCT(--ISNUMBER(B2:B7), --(((B2:B7 < MIN(E2, E3)) + (B2:B7 > MAX(E2, E3))) > 0)))Try the example
Use the original values or change them to test your understanding.