Getting rid of #DIV/0!

[erin]

Does anybody know a quick way of getting rid of the error message
#DIV/0!. Like a way to change it into just 0 or something? Thanks

Erin

 

[Guest]

Here are some typical ways....

For values in A1 and B1

Different ways depending on your anticipated values....
C1: =IF(B1<>0,A1/B1,0)
OR
C1: =IF(N(B1)<>0,A1/B1,0)
OR
C1: =IF(ISERROR(A1/B1),0,A1/B1)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

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

Vaya con Dios,
Chuck, CABGx3

 

[Harlan Grove]

Ron Coderre wrote...

Here are some typical ways.... ....
C1: =IF(ISERROR(A1/B1),0,A1/B1)

....

That would trap everything that could cause errors. Some kinds of
errors should appear as often as possible because they provide
important info that things are seriously wrong, e.g., #REF!, #NULL! and
#NAME? errors. If all you should trap are #DIV/0! errors, try

=IF(COUNT(1/(ERROR.TYPE(A1/B1)<>2)),A1/B1,0)

[And it's a shame there's no simpler way to check for particular types
of errors. There's no good reason ERROR.TYPE returns an #N/A error
rather than 0 when its argument's value isn't an error.]

 

[Sandy Mann]

=IF(COUNT(1/(ERROR.TYPE(A1/B1)<>2)),A1/B1,0)

this returns 0 for me regardless of any entry in A1 or B1 in XL97. Is it
different in other versions?

--
Regards,



Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Harlan Grove]

Sandy Mann wrote...

this returns 0 for me regardless of any entry in A1 or B1 in XL97. Is it
different in other versions?

....

Yeah, I screwed it up. So much for working from memory and not testing.
Try

=IF(COUNT(1/(ERROR.TYPE(A1/B1)=2)),0,A1/B1)

 

[Sandy Mann]

Yeah, I screwed it up. So much for working from memory and not testing.
Try

=IF(COUNT(1/(ERROR.TYPE(A1/B1)=2)),0,A1/B1)

Thank you Harlan,

With 199 days to got to retirement I find that my memory is going that way
too <g>


--

Sandy

In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Peo Sjoblom]

Congrats Sandy..

--

Peo

Thank you Harlan,

With 199 days to got to retirement I find that my memory is going that way
too <g>


--

Sandy

In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Peo Sjoblom]

To clarify, I meant congrats to you for you retirement, not for losing your
memory. <bg.>

 

[CLR]

"RETIREMENT"...........I knew there was something I forgot to do <g>

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

"RETIREMENT"...........I knew there was something I forgot to do <g>

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

In a couple of hours, it won't matter. Sandy won't remember either comments!

(From someone who hides his own Easter eggs.)

 

[Sandy Mann]

Thank you all you guys - when I relly do retire I can see I will have to
keep my big mouth shut! <g>

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk