how do I get AVERAGE to ingore a zero value cell

[Mikewoodmsw]

I am using the AVERAGE function but if i have two cells where one is "0" how
do I get it to preserve the first value rather than halving it?
E.G
what I need it to do is:
3+3 = 6, average score = 3
5+0 = 5 average score = 5

 

[Jason Morin]

One way:

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

If it's possible that text values are found in your range,
use:

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

This following is also valid, but longer:

=SUM(A1:A10)/-(COUNTA(A1:A10)-SUM(COUNTIF(A1:A10,
{"<>0","<=9.99999999999999E+307"})))

HTH
Jason
Atlanta, GA

 

[Gord Dibben]

Mike

Type =AVERAGE(IF(A1:A10<>0,A1:A10)) into a cell.

This is an array formula

Enter using CTRL + SHIFT + ENTER and Excel will place curly brackets around
like this.

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


Gord Dibben Excel MVP

 

[Mikewoodmsw]

Using the formula
=SUM(E2:F2)/COUNTIF(E2:F2,"<>0")

I can calcualte in cell G, the average of the two cells E2 and F2 so that if
either of those two cell values = zero it ignores the division by two.
e.g.

E F G
4 4 4
2 4 3
4 0 4

This works fine until the cell entry of "0" is imported from another
worksheet using the formula

=IF('other worksheet'!R3>=1,"0",('other worksheet'!R3))

in this instance the last cell entries are calculated
4 0 2

can anyone help please.

 

[Harlan Grove]

Mikewoodmsw wrote...

Using the formula
=SUM(E2:F2)/COUNTIF(E2:F2,"<>0") ....
This works fine until the cell entry of "0" is imported from another
worksheet using the formula

=IF('other worksheet'!R3>=1,"0",('other worksheet'!R3))

....

0 and "0" are different. The first is a number, the second a text
string. If you want this to 0 the number, change your formula to
=IF('other worksheet'!R3>=1,0,'other worksheet'!R3)