Average excluding zero

[desoriente]

I need to average a list of numbers, excluding the number zero. Example list: 1, 0, 2, 2, 0, 5, 0.

How can you average 1, 2, 2, 5 AND exclude the zeros from calculation?

Thanks!

 

[Michael M]

Hi there
Use

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

 

[desoriente]

Doesn't work. The sum is over two rows: C5:R5,C38:Q38. The countif gives me an INVALID.

 

[desoriente]

I figured it out from your example:
=SUM(C5:R5,C38:Q38)/(COUNTIF(C5:R5,">0")+COUNTIF(C38:Q38,">0"))

Thank you for your prompt response!

 

[Michael M]

Ok
Glad you got it sorted. Cheers.

 

[dave3009]

Or as an array formula confirmed with ctrl+shift+enter

=AVERAGE(IF(C5:R5<>0, C5:R5), IF(C38:Q38<>0, C38:Q38))