Zeros in text resulting in #div/0! even when using IF function
 

Ok, I'm nuts. I retrieve data constantly into an existing workbook.
Where the data is zero, when imported, it comes in as text, thus
causing #div/0! errors. Of course, I've included an IF function that
should return N/A or heck - I'd take a 'zero' at this point. Nadda.
Below is the formula I'm using and I'd appreciate an adjustment to get
this one right! =IF(D91=0,0,D71/D91). Function won't accept =IF(D91="
",0,D71/D91). Suggestion? Thx.

Zeros in text resulting in #div/0! even when using IF function
 

This might do it ...
=IF(AND(ISNUMBER(D91),D91<0),D71/D91,"More zero data!")
Rgds,
ScottO

wrote in message
oups.com...
| Ok, I'm nuts. I retrieve data constantly into an existing
workbook.
| Where the data is zero, when imported, it comes in as text, thus
| causing #div/0! errors. Of course, I've included an IF function
that
| should return N/A or heck - I'd take a 'zero' at this point.
Nadda.
| Below is the formula I'm using and I'd appreciate an adjustment to
get
| this one right! =IF(D91=0,0,D71/D91). Function won't accept
=IF(D91="
| ",0,D71/D91). Suggestion? Thx.
|

Zeros in text resulting in #div/0! even when using IF function
 

Try this instead:
=IF(isblank(D91),0,D71/D91)
or this:
=IF(iserror(d71/d91),0,d71/d91)

************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

wrote in message
oups.com...
Ok, I'm nuts. I retrieve data constantly into an existing workbook.
Where the data is zero, when imported, it comes in as text, thus
causing #div/0! errors. Of course, I've included an IF function that
should return N/A or heck - I'd take a 'zero' at this point. Nadda.
Below is the formula I'm using and I'd appreciate an adjustment to get
this one right! =IF(D91=0,0,D71/D91). Function won't accept =IF(D91="
",0,D71/D91). Suggestion? Thx.

Zeros in text resulting in #div/0! even when using IF function
 

Hi ScottO,

One thing that this formula won't do is to return the appropriate value
when there is real data vs text. Could I ask for a further suggestion?
Thx. again.

lk


ScottO wrote:
This might do it ...
=IF(AND(ISNUMBER(D91),D91<0),D71/D91,"More zero data!")
Rgds,
ScottO

wrote in message
oups.com...
| Ok, I'm nuts. I retrieve data constantly into an existing
workbook.
| Where the data is zero, when imported, it comes in as text, thus
| causing #div/0! errors. Of course, I've included an IF function
that
| should return N/A or heck - I'd take a 'zero' at this point.
Nadda.
| Below is the formula I'm using and I'd appreciate an adjustment to
get
| this one right! =IF(D91=0,0,D71/D91). Function won't accept
=IF(D91="
| ",0,D71/D91). Suggestion? Thx.
|

Zeros in text resulting in #div/0! even when using IF function
 

... appreciate an adjustment to get
this one right! =IF(D91=0,0,D71/D91).

Try this adjustment: =IF(D91+0=0,0,D71/D91)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Zeros in text resulting in #div/0! even when using IF function
 

=IF(N(D91),D71/D91,0)

Zeros in text resulting in #div/0! even when using IF function
 

Hi Max, I've tried the formula above and where I do have data, the
result returned = 0 vs the correct number. Do you have an alternate
suggestion? Thx.

Zeros in text resulting in #div/0! even when using IF function
 

Hi Annie, I've tried the formulas above and where I have actual data
vs. text, the results returned are 0 vs the appropriate answer. Do you
have an alternate suggestion? Thx.

Zeros in text resulting in #div/0! even when using IF function
 

Hi, I've tried the formula above. However, when I have actual data vs
text, the value returned is 0 vs the correct value. Do you have an
alternate suggestion? Thx.

Zeros in text resulting in #div/0! even when using IF function
 

wrote
.. Try this adjustment: =IF(D91+0=0,0,D71/D91)
Hi Max, I've tried the formula above and where I do have data, the
result returned = 0 vs the correct number. Do you have an alternate
suggestion? Thx.

Pl paste the actual data in D91 which returned the incorrect zero result
(instead of the correct D71/D91).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Zeros in text resulting in #div/0! even when using IF function
 

Looks like this thread is going
to remain an unsolved mystery .. <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---