Average that ignores zeros and blanks

average averageif

AVERAGEIF with not-equal-to-zero skips zeros so they do not drag the average down.

The problem

Zeros pull your average down.

Sample data

B2:B5 = 1000, 400, 200, 0.

Formula

=ROUND(AVERAGEIF(B2:B5,"<>0"),2)   -> 533.33

Plain AVERAGE would give 400 because the zero counts. AVERAGE already ignores truly blank cells.

Excel vs Sheets

Same in both.

Tested on sample data by the Cellwise checker (log CW-PL-20261018-42). AI-made: the text, voice and visuals are generated by AI.

Checked before publishing: a check.