how to remove #DIV/0!

[kuansheng]

Hi Guys,

I am trying to apply division on two cell and sometimes i will have 0
in both cell which will result in a 0 divided by 0 which excel will
prompt an error #DIV/0! . Snyone could advice me how can I replace the
error message with a 0 instead of #DIV/0!. What i am trying to get is
whenever there is a zero divide by a zero the result will show a zero
instead of #DIV/0! Thsnk you.

regards,
kuansheng

[Biff]

Hi!

One way:

=IF(ISERROR(A1/B1),0,A1/B1)

Biff

"kuansheng" <[email protected]> wrote in message
news:[email protected]...
> Hi Guys,
>
> I am trying to apply division on two cell and sometimes i will have 0
> in both cell which will result in a 0 divided by 0 which excel will
> prompt an error #DIV/0! . Snyone could advice me how can I replace the
> error message with a 0 instead of #DIV/0!. What i am trying to get is
> whenever there is a zero divide by a zero the result will show a zero
> instead of #DIV/0! Thsnk you.
>
> regards,
> kuansheng
>

[kuansheng]

Thanks Biff. That very helpful...

[Pete_UK]

Another way:

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

Hope this helps.

Pete

[kuansheng]

Thanks Pete. I tried both method they work like a charm. Learn
something new. Thanks

[Pete_UK]

Biff's method traps all errors and returns a zero instead of the error
message, whereas my method specifically avoids the #DIV/0 error, as you
requested. If A1 contained some text, then you would get the error
message #VALUE if you used my formula - just different approaches.

Pete

[daddylonglegs]

...or...

=IF(B1,A1/B1,0)

[kuansheng]

Thanks I have tried both method work great. Million thanks.

kuansheng