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

#### dmat2000

##### New Member
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:

Code:
``````=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.

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.

Code:
``````=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))``````

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!

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="".

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

Replies
2
Views
353
Replies
3
Views
137
Replies
3
Views
81
Replies
0
Views
195
Replies
3
Views
214

1,202,917
Messages
6,052,557
Members
444,592
Latest member
fauxlidae

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