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.]

On 2002-05-12 13:16, Kourada wrote:
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?

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

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination.

 

[Kourada]

Thanks, that works perfectly.....but why? I don't get it. Why was the zero ignored using that array formula?

 

[Dave Patton]

Array enter (Ctrl-Shift-Enter)one of the following:

=IF(COUNTIF(A1:C1,">0"),AVERAGE(IF(A1:C1=0,"",A1:C1)),"")

=IF(COUNTIF(A1:C1,">0")=0,"",AVERAGE(IF(A1:C1=0,"",A1:C1)))

=AVERAGE(IF(A1:C1,A1:C1)) gives error if all numbers are 0.

 

[Aladin Akyurek]

An efficient formula would be:

=SUM(A1:A3)/MAX(1,COUNT(A1:A3)-COUNTIF(A1:A3,0))

 

[Yogi Anand]

How about just

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

Regards!

 

[Aladin Akyurek]

On 2002-05-12 13:48, Yogi Anand wrote:

How about just

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

Regards!

You're excluding negative numbers. Also, you might run into #DIV/0!

 

[Yogi Anand]

You're excluding negative numbers. Also, you might run into #DIV/0!

Good thinking Aladin: I am revising it to:

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

regards!

 

[Mark W.]

On 2002-05-12 13:30, Kourada wrote:
Thanks, that works perfectly.....but why? I don't get it. Why was the zero ignored using that array formula?

When A1:A3 contains {5;0;5} the array formula, {=IF(A1:A3,A1:A3)}, produces {5;FALSE;5} because IF treats all non-zero values as TRUE and zero values as FALSE. The array formula, {=IF(A1:A3,A1:A3)}, returns the numbers (in A1:A3) themselves when TRUE and the boolean value, FALSE, when 0. The AVERAGE function ignores boolean (logical) values. So =AVERAGE({5;FALSE;5}) produces 5.
This message was edited by Mark W. on 2002-05-12 15:05

 

[Aladin Akyurek]

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

Try:

{0;0;0}

which are real 0's.