Statistical & Aggregation
Find MAX & MIN Values While Ignoring All Errors (Even in Negative Ranges)
Extract the true maximum and minimum finite numbers from ranges containing #DIV/0! or #N/A errors using MAXIFS and MINIFS without failing on all-negative readings.
Avoid the all-negative IFERROR(..., 0) trap in cold-chain and drawdown logs
When cold-storage freezer telemetry (-22C to -12C) or portfolio drawdown columns contain disconnected sensor #N/A or #DIV/0! errors, replacing errors with 0 produces a bogus maximum of 0C instead of the true warmest reading -12C!
Filtering finite numbers directly with MAXIFS(range, range, "<1E100") and MINIFS(range, range, ">-1E100") skips every error cell cleanly while preserving negative, zero, and positive extremes.
| Sensor Probe | Temp Reading (C) | MAXIFS("<1E100") | MINIFS(">-1E100") |
|---|---|---|---|
| Probe A | -18.5 | Candidate | Candidate |
| Probe B | #N/A (Offline) | Skipped | Skipped |
| Probe C | -12.2 | Max = -12.2 | Candidate |
| Probe D | #DIV/0! | Skipped | Skipped |
| Probe E | -21.4 | Candidate | Min = -21.4 |
Error-Safe Maximum Across Negative or Positive Numbers
Guards empty numeric ranges with COUNT(B2:B6) = 0 and returns the largest finite number via MAXIFS.
=IF(COUNT(B2:B6) = 0, "No Valid Data", MAXIFS(B2:B6, B2:B6, "<1E100"))Error-Safe Minimum Across Negative or Positive Numbers
Returns the smallest finite number via MINIFS while ignoring error cells.
=IF(COUNT(B2:B6) = 0, "No Valid Data", MINIFS(B2:B6, B2:B6, ">-1E100"))Try the example
Use the original values or change them to test your understanding.