# Return 0 rather than #DIV/O!

#### gossv

##### Board Regular
Hello, I have another problem!

I am using an "average if" formula and when there isn't a corresponding value it will return a #DIV/O!

I would like to know how to make it return a 0 figure rather than the #DIV/O!. I figure it would be something similar to the "IF ISNA" command

=AVERAGE(IF('MI April - June 2006 (2)'!\$K\$5:\$K\$20000=Report!\$A30,IF('MI April - June 2006 (2)'!\$G\$5:\$G\$20000=Report!F\$5,'MI April - June 2006 (2)'!\$C\$5:\$C\$20000)))

Ginny

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try

=if(iserror(AVERAGE(IF('MI April - June 2006 (2)'!\$K\$5:\$K\$20000=Report!\$A30,IF('MI April - June 2006 (2)'!\$G\$5:\$G\$20000=Report!F\$5,'MI April - June 2006 (2)'!\$C\$5:\$C\$20000)))), 0, AVERAGE(IF('MI April - June 2006 (2)'!\$K\$5:\$K\$20000=Report!\$A30,IF('MI April - June 2006 (2)'!\$G\$5:\$G\$20000=Report!F\$5,'MI April - June 2006 (2)'!\$C\$5:\$C\$20000))))

There's probably a better way around this, but it's what I know

Thank you big monkey, it works beautifully!

Confirmed with control+shift+enter...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,AVERAGE(IF('MI April - June 2006 (2)'!\$K\$5:\$K\$20000=Report!\$A30,IF('MI April - June 2006 (2)'!\$G\$5:\$G\$20000=Report!F\$5,'MI April - June 2006 (2)'!\$C\$5:\$C\$20000)))))

could be faster.

Replies
1
Views
104
Replies
0
Views
127
Replies
1
Views
301
Replies
5
Views
306
Replies
5
Views
261

1,219,580
Messages
6,149,115
Members
450,861
Latest member
metcala

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