Make Function ignore 0 and error values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AD108 - 04 Nov 2006 18:53 GMT

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

Reply to this Message

Teethless mama - 04 Nov 2006 19:33 GMT

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

Array formula you have to commit ctrl>shift>enter (not just enter)

> 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
[quoted text clipped - 9 lines]
> Remove 7 numbers to email
> AD108

Reply to this Message

Teethless mama - 04 Nov 2006 19:45 GMT

Non array enter

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

Reply to this Message

AD108 - 04 Nov 2006 20:03 GMT

Thanks very much,

> Non array enter
>
[quoted text clipped - 13 lines]
> > Remove 7 numbers to email
> > AD108

Reply to this Message