function for average without zero

K

kml

Hi

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

Thanks in advance.

kml
 
G

Gary's Student

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
 
R

Robert_Steel

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

should do what you need

hth RES
 
Top