# Using AVERAGEIF & ISERROR together

#### 28creation

##### Board Regular
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.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I think you want this:

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

Hi,

Try this

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

Yes!! Thank you very much!

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.

Excel Formula:
``=IFERROR(AVERAGEIF(\$D\$9:\$D\$1508,F\$2,\$I\$9:\$I\$1508),0)``

Excel Formula:
``=IFERROR(AVERAGEIF(\$D\$9:\$D\$1508,F\$2,\$I\$9:\$I\$1508),0)``
Perfect! Awesome! Thank you!

You're welcome & thanks for the feedback.

Replies
3
Views
225
Replies
6
Views
960
Replies
1
Views
128
Replies
2
Views
181
Replies
2
Views
251

1,203,506
Messages
6,055,796
Members
444,825
Latest member
aggerdanny

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back