Logical & Conditional
Flag & Count Values NOT Between X and Y (OR & Two-Tail COUNTIF)
Detect cold-chain, blood-glucose, or machining readings that fall strictly outside an acceptable [Min, Max] window using OR(B2 < MIN(X,Y), B2 > MAX(X,Y)) and count total two-sided excursions via COUNTIF.
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!
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 | In-Spec Safe Storage [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.
=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
Adds low-tail violations (< MIN) and high-tail violations (> MAX) across the sensor log.
=COUNTIF(B2:B7, "<" & MIN(E2, E3)) + COUNTIF(B2:B7, ">" & MAX(E2, E3))Try the example
Use the original values or change them to test your understanding.