# How to make #DIV/0! Invisible?!

#### LaurenHancy

##### Board Regular
Hello,

I am using this formular to find the average for a certain products but it comes back with a DIV/0!. I still want the formular to be in the back ground as when i rerun the report it may come back with a figure.

This is the current formular i am using:

{=AVERAGE(IF(\$J\$20:\$J\$201=B5,\$AI\$20:\$AJ\$201))}

Hope you can help.

Many thanks

Lauren

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Stu Dapples

##### Active Member
Try this:

{=IF(ISERROR(AVERAGE(IF(\$J\$20:\$J\$201=B5,\$AI\$20:\$AJ\$201)),"",AVERAGE(IF(\$J\$20:\$J\$201=B5,\$AI\$20:\$AJ\$201))}

Should do the trick but never used with the curly braces before...

#### LaurenHancy

##### Board Regular
Thanks for the fast reply. Unfortunately it doesn't work. It doesnt even bring a error message back, just:

{=IF(ISERROR(AVERAGE(IF(\$J\$20:\$J\$201=B5,\$AI\$20:\$AJ\$201)),"",AVERAGE(IF(\$J\$20:\$J\$201=B5,\$AI\$20:\$AJ\$201))}

Any other ideas?

Thanks

L

#### Stu Dapples

##### Active Member
Dont type in the curly brace {}, I'm not sure why you need to use them anyway but if you do, enter the formula then hold ctrl, shift and hit enter, excel will add the braces for you!

I think it is something to do with the way in which excel handles the formula, something about iterations

I think you formula will work without, give it a try....

#### Jonmo1

##### MrExcel MVP
You're missing a couple ))

try

=IF(ISERROR(AVERAGE(IF(\$J\$20:\$J\$201=B5,\$AI\$20:\$AJ\$201))),"",AVERAGE(IF(\$J\$20:\$J\$201=B5,\$AI\$20:\$AJ\$201)))

enterd with CTRL + SHIFT + ENTER

#### LaurenHancy

##### Board Regular
Thats worked a treat, many thanks for all your help guys.

Lx

Replies
4
Views
598
Replies
20
Views
417
Replies
0
Views
95
Replies
3
Views
537
Replies
8
Views
1K

1,191,719
Messages
5,988,298
Members
440,148
Latest member
sandy123

### 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