Getting rid of #DIV/0!

E

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
 
R

Ron Coderre

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
 
H

Harlan Grove

Ron Coderre wrote...
Here are some typical ways.... ....
C1: =IF(ISERROR(A1/B1),0,A1/B1)
Click to expand...
....

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.]
 
H

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?
Click to expand...
....

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)
 
S

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)
Click to expand...

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

[email protected]
[email protected] with @tiscali.co.uk
 
P

Peo Sjoblom

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

CLR

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

Vaya con Dios,
Chuck, CABGx3
 
C

CLR

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

Vaya con Dios,
Chuck, CABGx3
 
D

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.)
 
Top