Statistical
Average ignoring zeros: exclude 0 placeholders without counting blanks
Use AVERAGEIF(range, "<>0") to skip both blank cells and zero-output shutdown days—and guard all-zero ranges cleanly.
Why 0 drags AVERAGE down while blanks do not
Standard AVERAGE ignores blank cells automatically, so a blank Saturday does not lower a factory's daily average. However, if an ERP feed exports 0 for unworked weekend days, AVERAGE includes those zeros in the denominator!
AVERAGEIF(B2:B6, "<>0") excludes both 0s AND blank cells—averaging strictly non-zero active days (including negative adjustments; use ">0" if you want strictly positive days).
| Day (A) | UnitsProduced (B) | Included in AVERAGEIF("<>0")? |
|---|---|---|
| Mon | 240 | Yes |
| Tue (Shutdown) | 0 | Skipped |
| Wed | 260 | Yes |
| Thu (Shutdown) | 0 | Skipped |
| Fri | 250 | Yes |
Compare AVERAGEIF("<>0") = 250 vs AVERAGE = 150
Active output across Mon, Wed, Fri averages (240 + 260 + 250) / 3 = 250 units/day, whereas raw AVERAGE(B2:B6) reports 750 / 5 = 150.
=AVERAGEIF(B2:B6, "<>0")Guard all-zero weeks against #DIV/0!
If every day in the range is 0, zero cells match "<>0" and AVERAGEIF returns #DIV/0!. Wrap with IFERROR(..., 0) for dashboard cards.
=IFERROR(AVERAGEIF(B2:B6, "<>0"), 0)Try the example
Use the original values or change them to test your understanding.