Average with 0s and without 0s

[timspin]

Hi All,

I have a column of numbers, including cells that contain a “0,” and I want to AVERAGE the column both ways—including the zeros and then ignoring the zeros. How can I write a formula that will average the column but ignore the zeros? [Excel already ignores BLANK cells when doing averages, but I don’t want the cells to be blank, I want to show the viewer that there is a zero in it.]

Any help gratefuly recieved
Thanks
Tim

 

[Jonmo1]

You could do

=SUM(A1:A10)/COUNTIF(A1:A10,">0")

Assuming there are no negative numbers...

 

[VoG]

Try

=AVERAGE(IF(A1:A4<>0,A1:A4))

Confirmed with CTRL + Shift + Enter, not just enter.

 

[number kruncher]

Or try..

=AVERAGEIF(range,">0")

 

[timspin]

Excellent as always - thanks guys Im off to plug it in and see what happens.