Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. | |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Zeros in text resulting in #div/0! even when using IF function
=IF(N(D91),D71/D91,0)
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text Function with Different Formatting for Number | Excel Discussion (Misc queries) | |||
adding zero's to a text cell | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
zero supress leading zeros when chg format from text to number | Excel Worksheet Functions | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) |