Statistical & Aggregation
Average Non-Zero Values While Keeping or Excluding Negatives (AVERAGEIF)
Exclude placeholder 0.00 cells from KPI and margin averages using AVERAGEIF(range, "<>0") vs. strictly positive AVERAGEIF(range, ">0") with #DIV/0! protection.
Why "<>0" and ">0" give completely different answers when credit memos or negative variances exist!
Unclosed fiscal months and inactive SKU rows often contain formula-returned zeros (0) rather than true blanks—dragging standard AVERAGE(B2:B7) down artificially! Using AVERAGEIF(B2:B7, "<>0") skips every 0 while preserving legitimate negative adjustments (such as -$30 rebates or net losses), whereas AVERAGEIF(B2:B7, ">0") averages strictly positive entries only!
Compare six monthly net margin entries [120, 0, -30, 150, 0, 120]: naive AVERAGE(B2:B7) underreports 60.00, non-zero AVERAGEIF(B2:B7, "<>0") reports true active-month net performance (120 - 30 + 150 + 120) / 4 = 90.00, and positive-only AVERAGEIF(B2:B7, ">0") reports gross winning-month average 130.00!
| Aggregation Rule on [120, 0, -30, 150, 0, 120] | Included Cells | Computed Mean | Accounting Use Case |
|---|---|---|---|
| AVERAGEIF(B2:B7, "<>0") | 4 non-zero cells (keeps -30) | 90.00 | Active-month net P&L / variance tracking |
| AVERAGEIF(B2:B7, ">0") | 3 positive cells (drops 0 and -30) | 130.00 | Gross positive-sales benchmarking |
| All Zeros [0, 0, 0, 0, 0, 0] | 0 matching cells | "All Values Zero" | Prevents #DIV/0! crash |
Average All Non-Zero Numbers (Preserving Negative Entries)
Averages B2:B7 where values are not equal to zero ("<>0"), guarding against all-zero ranges with COUNTIF.
=IF(COUNTIF(B2:B7, "<>0") = 0, "All Values Zero", ROUND(AVERAGEIF(B2:B7, "<>0"), 2))Average Strictly Positive Numbers Only (">0")
Excludes both zeros and negative entries when benchmarking strictly positive production or billing days.
=IF(COUNTIF(B2:B7, ">0") = 0, "No Positive Values", ROUND(AVERAGEIF(B2:B7, ">0"), 2))Try the example
Use the original values or change them to test your understanding.