ignoring zero values in calculations

[Kourada]

I cells A1:A3 I have the numbers 5,0,5 respectivly. In Cell A4, I have the formula Average(A1:A3). I want the zero value to be ignored in the calculation, so that the result of the average is "5". Data is continuously added to the spreadsheet, and I never know which of the cells will have a zero value, so just averaging cells A1 and A3 is not an option.

Any ideas?

 

[Mark W.]

Just use...

{=AVERAGE(IF(A1:A3,A1:A3))}

...and bypass all of these concerns.

 

[Yogi Anand]

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

Try:

{0;0;0}

which are real 0's.

Hi Aladin:

the following modification will take care of concern about the possibility of all 0s as well.

=IF(SUM(A1:A3)=0,0,(SUM(A1:A3)/COUNTIF(A1:A3,"<>0")))

Regards!
This message was edited by Yogi Anand on 2002-05-12 17:23

 

[Kourada]

Thanks to everyone for all the input and explanations.