If results in #div/0. How can I make it default to 0?

[dmat2000]

Hello,

I am under pressure to get this report finalized within an hour. So I am hoping for some quick guidance!

Here's the problem, I am use an if with an and statement. Some of the cells that are dividing 0 result in a #div/0. How do I supress the #div/o and display a 0?

Here are the four different if statements I am using in this template:

=IF(E9/B9<=$H$3,E9*$H$4,0)
=IF(AND((E9/B9)>=$I$2,(E9/B9)<=$I$3),E9*$I$4,0)
=IF(AND((E9/B9)>=$J$2,(E9/B9)<=$J$3),E9*$J$4,0)
=IF((E9/B9)>=$K$2,E9*$K$4,0)

The if statements work fine as long as it isn't dividing by 0.... I've been trying to use an IsError formula but haven't been successful.

Please help!

 

[buxtongt]

Welcome to MrExcel.

Try:

=IF(ISERROR(IF(E9/B9<=$H$3,E9*$H$4,0)=TRUE),"0",IF(E9/B9<=$H$3,E9*$H$4,0))

Hope this helps.

 

[FKoenig]

How about:

=IF(ISERROR(E9/B9),0,IF(E9/B9<=$H$3,E9*$H$4,0))
=IF(ISERROR(E9/B9),0,IF(AND((E9/B9)>=$I$2,(E9/B9)<=$I$3),E9*$I$4,0))
=IF(ISERROR(E9/B9),0,IF(AND((E9/B9)>=$J$2,(E9/B9)<=$J$3),E9*$J$4,0))
=IF(ISERROR(E9/B9),0,IF((E9/B9)>=$K$2,E9*$K$4,0))

 

[dmat2000]

Thank you FKoenig! I know I wasn't far off. I was missing the IF and the last , in the nested if. Appreciate the help!

 

[buxtongt]

Good call FKoenig, it looks like that e9/b9 is the only thing that could be throwing the error. You could probably even reduce it to if b9=0 or b9="".

 

[FKoenig]

Welcome to the boards. Thank you for the positive feedback.