How to remove #DIV/0! appearing

[whiteevo4]

Hello everyone,

I was wondering if you could help me with removing the #DIV/0! to clear? but still able to keep to formula there.

Any ideas would be great.

Thanks.

 

[T. Valko]

Hello everyone,

I was wondering if you could help me with removing the #DIV/0! to clear? but still able to keep to formula there.

Any ideas would be great.

Thanks.

You will get a better answer if you tell us what version of Excel you're using and show us the formula.

 

[Xtrimmer]

You could try:

=IFERROR(your_original_formula,"")

Important Note: The IFERROR function was created for Excel 2007. If you use earlier versions of Excel the function ISERROR will not work, it will just generate an error message #NAME. So use the formula IF/ISERROR function instead as it works in all versions of Excel.​

 

[Scott Huish]

There is no need for IF ISERROR in earlier versions. Just check for a zero denominator.

The basic formula is like this:

=IF(B1,A1/B1,"")

 

[Xtrimmer]

Yes,

I got this to work also:

=IF(B1 < > 0,(A1/B1),"")

 

[whiteevo4]

Hey..thanks for the great ideas. It would clear it to 0. Maybe I need to explain myself better. Below is what I have. I have the 'B' column dividing a 2 number from 2 different piviot tables. So that formula I've used in column 'B' is '='UNITS DEL'!B50/'UNITS ORD'!B50'
A B
<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=195 border=0 x:str><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=116 height=17>CEXP</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle width=79 x:err="#DIV/0!">#DIV/0!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>COKE</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="1">100.00%</TD></TR></TBODY></TABLE>

I hope this helps and thanks again everyone.

 

[T. Valko]

Hey..thanks for the great ideas. It would clear it to 0. Maybe I need to explain myself better. Below is what I have. I have the 'B' column dividing a 2 number from 2 different piviot tables. So that formula I've used in column 'B' is '='UNITS DEL'!B50/'UNITS ORD'!B50'
A B
<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=195 border=0 x:str><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=116 height=17>CEXP</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle width=79 x:err="#DIV/0!">#DIV/0!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>COKE</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="1">100.00%</TD></TR></TBODY></TABLE>

I hope this helps and thanks again everyone.

What result do you want to replace the #DIV/0! error?

You still didn't tell us what version of Excel you're using.

Try one of these.

For a result of 0:

=IF('UNITS ORD'!B50=0,0,'UNITS DEL'!B50/'UNITS ORD'!B50)

For a result of blank:

=IF('UNITS ORD'!B50=0,"",'UNITS DEL'!B50/'UNITS ORD'!B50)

 

[whiteevo4]

biff, you are too good. Sorry I was running the old version...04 i think. Thanks and thank you everyone!! case closed.

 

[T. Valko]

biff, you are too good. Sorry I was running the old version...04 i think. Thanks and thank you everyone!! case closed.

You're welcome. Thanks for the feedback!