I am trying to make the average function ignore 0's and errors. The
following array formula works to ignore the 0's, but I got stuck trying to
make it ignore errors.

=AVERAGE(IF((G3:G20)<0,G3:G20))

=AVERAGE(IF(AND((G3:G20)<0,ISERROR(G3:G20)=FALSE) ,G3:G20))

Thanks in advance.



--
Remove 7 numbers to email
AD108

=AVERAGE(IF(ISERROR(G3:G20),"",IF(G3:G20<0,G3:G20 ))

Array formula you have to commit ctrlshiftenter (not just enter)


"AD108" wrote:

I am trying to make the average function ignore 0's and errors. The
following array formula works to ignore the 0's, but I got stuck trying to
make it ignore errors.

=AVERAGE(IF((G3:G20)<0,G3:G20))

=AVERAGE(IF(AND((G3:G20)<0,ISERROR(G3:G20)=FALSE) ,G3:G20))

Thanks in advance.



--
Remove 7 numbers to email
AD108

Non array enter

=SUMIF(G3:G20,"0")/COUNTIF(G3:G20,"0")



"AD108" wrote:

I am trying to make the average function ignore 0's and errors. The
following array formula works to ignore the 0's, but I got stuck trying to
make it ignore errors.

=AVERAGE(IF((G3:G20)<0,G3:G20))

=AVERAGE(IF(AND((G3:G20)<0,ISERROR(G3:G20)=FALSE) ,G3:G20))

Thanks in advance.



--
Remove 7 numbers to email
AD108

Thanks very much,


"Teethless mama" wrote in message
...
Non array enter

=SUMIF(G3:G20,"0")/COUNTIF(G3:G20,"0")



"AD108" wrote:

I am trying to make the average function ignore 0's and errors. The
following array formula works to ignore the 0's, but I got stuck trying
to
make it ignore errors.

=AVERAGE(IF((G3:G20)<0,G3:G20))

=AVERAGE(IF(AND((G3:G20)<0,ISERROR(G3:G20)=FALSE) ,G3:G20))

Thanks in advance.



--
Remove 7 numbers to email
AD108