function for average without zero

[Guest]

Hi

I'd like to create a function that would calculate the average without the
zero(s)?

Thanks in advance.

kml

 

[Guest]

There are two solutions:

1. replace the zeros with blanks. AVERAGE will ignore blanks

2. use SUMIF() divided by COUNTIF() excluding zeros in each of the functions

 

[Aladin Akyurek]

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

 

[Robert_Steel]

=average(if((A1:A10<>0,A1:A10,"")
array entered (ctl, shift, enter)

should do what you need

hth RES