function for average without zero
 

Hi

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

Thanks in advance.

kml

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
--
Gary's Student


"kml" wrote:

Hi

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

Thanks in advance.

kml

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

kml wrote:
Hi

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

Thanks in advance.

kml

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

should do what you need

hth RES