Replacing #DIV/0! WITH "0"

B

Bush Kangaroo

Afternoon all,

I have a formula that could potentially return #DIV/0! should this b
the case I would like for the sake of tidiness replace #DIV/0! wit
"0"

Any tips

Your help is greatly appreciate
 
G

gilmores33

Jammy,

I'm having the same issue. Are you saying use that if formula as
conditional formula? I tried using your formula as conditional and i
doesn't seem to work.

Any suggestions.

K
 
J

jammy

in the cells where you have your original formula use the one
suggest.

e.g. you have 3 columns,
A B C
1 1 5 =a1/b1
2 6 2 =a2/b2
3 9 0 =a3/b3 --> #DIV/0!

so instead of the formula in column C, you use

=if(iserror(a1/b1),0,a1/b1), etc

basically saying, if my formula is going to bring up an error (an
error) then use 0 otherwise use the value from the formula
 
G

gilmores33

Jammy,

I apologize if I seem like a rookie, as I actually thought I was prett
good with excel until I found this website. I'm having a difficul
time with your example.

Here's what I've got.

Columns B through G are involved.

The cell that shows the error is in column G. The formula in G tha
gives the error involves cells in columns B, C, D, and E. Colunns
and E have formulas where as the rest are manually inputed wit
figures.

I am just starting to learn about conditional formulas. Could yo
please tell me one more time, where to put what?

I appreciate the help.

K
 
J

jammy

If the formula you have in column G does what you want (apart from th
div by 0 error) then all you need to do is just copy the exact formul
from column G (minus the =) then put it inside the if statement.

say for example the formula in G is something like

=((B+C)*D)/E

and this works fine but sometimes E is actually a 0 value and so bring
up the error.

what you need is

=if(iserror(((B+C)*D)/E),0,((B+C)*D)/E)

it is exactly the same formula as your original, but simply with a
exception condition saying if my formula brings up an error value put
into the cell instead of the error, otherwise put the value calculated
 
Top