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

[lkulchak]

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.

 

[ScottO]

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

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

 

[Anne Troy]

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

 

[lkulchak]

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

 

[Max]

... appreciate an adjustment to get

this one right! =IF(D91=0,0,D71/D91).

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


---

 

[akyurek]

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

 

[lkulchak]

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.

 

[lkulchak]

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.

 

[lkulchak]

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.

 

[Max]

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]

Looks like this thread is going
to remain an unsolved mystery .. <g>

---