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.

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

<lkulchak@austin.rr.com> wrote in message
news:1141011000.314635.111680@p10g2000cwp.googlegroups.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.
|

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@austin.rr.com> wrote in message
news:1141011000.314635.111680@p10g2000cwp.googlegroups.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.
>

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
>
> <lkulchak@austin.rr.com> wrote in message
> news:1141011000.314635.111680@p10g2000cwp.googlegroups.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.
> |

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

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

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.

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.

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.

<lkulchak@austin.rr.com> 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
---

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