How to average a range while ignoring zeros (and blanks)
✓ Verified in LibreOffice 25.8.7.3Get the mean of only the non-zero numbers, so empty or zero cells don't drag the average down.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =AVERAGEIF(A2:A6,"<>0") | Blanks are already ignored by AVERAGE/AVERAGEIF; this also drops zeros. |
| Google Sheets | =AVERAGEIF(A2:A6,"<>0") | Identical. |
| LibreOffice Calc | =AVERAGEIF(A2:A6,"<>0") | Identical. |
How it works
AVERAGEIF(range, "<>0") averages only the cells that are not equal to zero. With 10/0/20/0/30 it averages 10, 20 and 30 = 20. Blank cells are ignored automatically.
Verified, not just documented
We ran =AVERAGEIF(A2:A6,"<>0") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 20 — exactly the expected result. Every formula here is confirmed by actually executing it.