Getting rid of #DIV/0! #2

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

0
fujihamma (4)
6/2/2006 1:43:20 PM
excel 39879 articles. 2 followers. Follow

12 Replies
515 Views

Similar Articles

[PageSpeed] 34

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


"erin" wrote:

> 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
> 
> 
0
6/2/2006 1:53:01 PM
=IF(B1,A1/B1,"")

Vaya con Dios,
Chuck, CABGx3



"erin" wrote:

> 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
> 
> 
0
CLR (807)
6/2/2006 5:40:02 PM
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.]

0
hrlngrv (1990)
6/2/2006 7:40:35 PM
"Harlan Grove" <hrlngrv@aol.com> wrote in message 
news:1149277235.481617.321960@i40g2000cwc.googlegroups.com...

> =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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk



0
sandymann2 (1054)
6/2/2006 8:44:46 PM
Sandy Mann wrote...
>"Harlan Grove" <hrlngrv@aol.com> wrote in message
>>=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?
....

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)

0
hrlngrv (1990)
6/2/2006 9:00:02 PM
"Harlan Grove" <hrlngrv@aol.com> wrote in message 
news:1149282002.362339.30680@h76g2000cwa.googlegroups.com...

> 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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


0
sandymann2 (1054)
6/2/2006 9:29:23 PM
Congrats Sandy..

-- 

Peo



"Sandy Mann" <sandymann2@mailinator.com> wrote in message 
news:OvD26uohGHA.896@TK2MSFTNGP02.phx.gbl...
>
> "Harlan Grove" <hrlngrv@aol.com> wrote in message 
> news:1149282002.362339.30680@h76g2000cwa.googlegroups.com...
>
>> 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
>
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
> 


0
Peo
6/3/2006 1:43:35 AM
To clarify, I meant congrats to you for you retirement, not for losing your 
memory. <bg.>

-- 

Peo



"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message 
news:%23uySm8qhGHA.1856@TK2MSFTNGP03.phx.gbl...
> Congrats Sandy..
>
> -- 
>
> Peo
>
>
>
> "Sandy Mann" <sandymann2@mailinator.com> wrote in message 
> news:OvD26uohGHA.896@TK2MSFTNGP02.phx.gbl...
>>
>> "Harlan Grove" <hrlngrv@aol.com> wrote in message 
>> news:1149282002.362339.30680@h76g2000cwa.googlegroups.com...
>>
>>> 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
>>
>> sandymann2@mailinator.com
>> Replace@mailinator.com with @tiscali.co.uk
>>
>>
>
> 


0
Peo
6/3/2006 1:49:52 AM
"RETIREMENT"...........I knew there was something I forgot to do <g>

Vaya con Dios,
Chuck, CABGx3


"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:OvD26uohGHA.896@TK2MSFTNGP02.phx.gbl...
>
> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> news:1149282002.362339.30680@h76g2000cwa.googlegroups.com...
>
> > 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
>
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>


0
croberts (1377)
6/3/2006 1:56:12 AM
"RETIREMENT"...........I knew there was something I forgot to do <g>

Vaya con Dios,
Chuck, CABGx3


"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:OvD26uohGHA.896@TK2MSFTNGP02.phx.gbl...
>
> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> news:1149282002.362339.30680@h76g2000cwa.googlegroups.com...
>
> > 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
>
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>




0
croberts (1377)
6/3/2006 1:56:43 AM
In a couple of hours, it won't matter.  Sandy won't remember either comments!

(From someone who hides his own Easter eggs.)

Peo Sjoblom wrote:
> 
> To clarify, I meant congrats to you for you retirement, not for losing your
> memory. <bg.>
> 
> --
> 
> Peo
> 
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:%23uySm8qhGHA.1856@TK2MSFTNGP03.phx.gbl...
> > Congrats Sandy..
> >
> > --
> >
> > Peo
> >
> >
> >
> > "Sandy Mann" <sandymann2@mailinator.com> wrote in message
> > news:OvD26uohGHA.896@TK2MSFTNGP02.phx.gbl...
> >>
> >> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> >> news:1149282002.362339.30680@h76g2000cwa.googlegroups.com...
> >>
> >>> 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
> >>
> >> sandymann2@mailinator.com
> >> Replace@mailinator.com with @tiscali.co.uk
> >>
> >>
> >
> >

-- 

Dave Peterson
0
petersod (12005)
6/3/2006 11:48:25 AM
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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:44817709.E84815EE@verizonXSPAM.net...
> In a couple of hours, it won't matter.  Sandy won't remember either 
> comments!
>
> (From someone who hides his own Easter eggs.)
>
> Peo Sjoblom wrote:
>>
>> To clarify, I meant congrats to you for you retirement, not for losing 
>> your
>> memory. <bg.>
>>
>> --
>>
>> Peo
>>
>> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
>> news:%23uySm8qhGHA.1856@TK2MSFTNGP03.phx.gbl...
>> > Congrats Sandy..
>> >
>> > --
>> >
>> > Peo
>> >
>> >
>> >
>> > "Sandy Mann" <sandymann2@mailinator.com> wrote in message
>> > news:OvD26uohGHA.896@TK2MSFTNGP02.phx.gbl...
>> >>
>> >> "Harlan Grove" <hrlngrv@aol.com> wrote in message
>> >> news:1149282002.362339.30680@h76g2000cwa.googlegroups.com...
>> >>
>> >>> 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
>> >>
>> >> sandymann2@mailinator.com
>> >> Replace@mailinator.com with @tiscali.co.uk
>> >>
>> >>
>> >
>> >
>
> -- 
>
> Dave Peterson 


0
sandymann2 (1054)
6/3/2006 12:43:54 PM
Reply:

Similar Artilces: