Using AVERAGEIF & ISERROR together

[28creation]

Hi all,


I'm producing an average of G9-K9, but only if the result is over 0, so I have this initial formula:

=AVERAGEIF(G9:K9,"<>0")

This works fine.


But I'm trying to include an ISERROR so if there's no results in the boxes specified the cell will produce a blank box rather than #DIV/0!.

I've tried to use an example formula off another spreadsheet & it's not working:

=AVERAGEIF(ISERROR(G9:K9,"<>0"),"",G9:K9).


Hope someone can point me in the right direction.

Thanks.

 

[Colin Legg]

I think you want this:

=IFERROR(AVERAGEIF(G9:K9,"<>0"),"")

 

[Stuart Little]

Hi,

Try this

=AVERAGEIF(G9:K9,"<>"&0,G9:K9)

 

[28creation]

Yes!! Thank you very much!

 

[Unlucky]

Trying to remove the #DIV/0! error for zeros in an AVERAGEIF formula with a specific search criteria from another table. The formula I am using is:

=AVERAGEIF($D$9:$D$1508,"="&F$2,$I$9:$I$1508)

. . . but I can't figure out how to get the calculation to report a zero instead of the #DIV/0! error. I tried incorporating the ISERROR but can't get the formula right.

 

[Fluff]

How about

=IFERROR(AVERAGEIF($D$9:$D$1508,F$2,$I$9:$I$1508),0)

 

[Unlucky]

How about

=IFERROR(AVERAGEIF($D$9:$D$1508,F$2,$I$9:$I$1508),0)

Perfect! Awesome! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.