average without 0 value

[Alex]

Hi everybody,

I have a column as follows:
12%
0
10%
0
0
5%
The aver. % will be 9%. But, if we take 0 into consideration it will be 4.5%.

How could I the Average formula without taking this 0 into consideration?

Thanks

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100<>0,A1:A100))

 

[Domenic]

=AVERAGE(IF(A1:A6<>0,A1:A6))

...entered using CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Alex]

Thanks, but I'm getting #VALUE!.

=AVERAGE(IF(A1:A6<>0,A1:A6))

...entered using CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Frank Kabel]

Hi
as said: enter this as array formula with CTRL+SHIFT+ENTER

 

[anilsolipuram]

try this



=( SUM(A1:A6)/ ( COUNT(A1:A6)-COUNTIF(A1:A6,0) ) )


Let me know if your problem is not resolved