Disregard 0 in an average calc

[MRomanow]

I have 14 cells that I paste in numbers from another program. I then have a formula that calculates the average of these 14 numbers. Sometimes the numbers that I copy and paste include a few zeros in them. How can I get the average calculation to disregard these zeros?

 

[barry houdini]

use

=AVERAGE(IF(range,range))

confirmed with CTRL+SHIFT+ENTER

or with just ENTER

=SUM(range)/SUMPRODUCT(--(range<>0))

 

[NBVC]

Try

=AVERAGE(IF(A1:A10<>0,A1:A10,FALSE))

Confirmed with CTRL+SHIFT+ENTER, not just Enter

Adjust range to suit.

 

[MRomanow]

Thanks! That worked great!

 

[al_b_cnu]

Hi,

an alternative without using [Ctl-Shift-enter] is
=SUM(A1:A10)/COUNTIF(A1:A10,"<>0")

 

[Brian from Maui]

Try,

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