Thanks:  0
Likes:  0

# Thread: ignoring zero values in calculations

1. 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?

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

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

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

5. An efficient formula would be:

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

6. How about just

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

Regards!

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

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

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

8. 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!

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

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

Try:

{0;0;0}

which are real 0's.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•