Statistical & Aggregation
Average a Range While Ignoring #DIV/0! and #N/A Errors (AVERAGEIF)
Compute the exact arithmetic mean of valid numeric cells in a range containing formula errors (#DIV/0!, #N/A, #VALUE!) using AVERAGEIF(range, "<1E100") without distorting zeros.
Why AVERAGEIF(range, "<1E100") beats IFERROR(range, 0)
Plain AVERAGE(B2:B6) fails immediately if even one row contains #DIV/0! or #N/A. Worse, replacing errors with 0 via IFERROR(cell, 0) injects artificial zeros into the denominator and drags the mean downward!
Passing the numeric criterion "<1E100" to AVERAGEIF(B2:B6, "<1E100") instructs the engine to average every finite real number (positive, zero, or negative) while skipping #DIV/0!, #N/A, text labels, and blanks completely. Guarding with COUNT(B2:B6) = 0 prevents a secondary #DIV/0! when every row is an error.
| Store Row | Margin Cell | Included by AVERAGEIF("<1E100")? | Naive IFERROR(...,0) Distortion |
|---|---|---|---|
| Store 1 | 120 | Yes (120) | 120 |
| Store 2 | #DIV/0! | Ignored cleanly | Treated as 0 (wrong!) |
| Store 3 | 150 | Yes (150) | 150 |
| Store 4 | #N/A | Ignored cleanly | Treated as 0 (wrong!) |
| Store 5 | 90 | Yes (90) -> Mean = 120 | 90 -> Distorted Mean = 72 |
Error-Proof Mean of Finite Numbers
Checks that at least one numeric cell exists via COUNT(B2:B6) and averages finite numbers via AVERAGEIF.
=IF(COUNT(B2:B6) = 0, "No Valid Readings", ROUND(AVERAGEIF(B2:B6, "<1E100"), 2))Valid Numeric Sample Size vs Error Cell Count
Counts how many finite numeric observations contributed to the average.
=COUNT(B2:B6)Try the example
Use the original values or change them to test your understanding.