G
Guest
I posted this as a General Question several days ago, but the answers I
received did not help. I have recently begun to experience random problems
with conditional IF formulas not returning zero but a very very tiny negative
number which causes wrong results in other cells which require a zero value
from the IF formula to calculate correctly. I have never experienced this
problem before. It started in Office XP, but I have now upgraded to Office
2003 and the error is still there. An example of the formula is:
=IF(D467>=$D$25,IF(G467>0,0,G467),IF(E467+G467*B467*24*60*60/1000000>$D$26,($D$26-E467)*1000000/(B467*24*60*60),G467))
The error occurs with the first nested IF because it should enter zero when
G467>0, but occasionally (twice in 1000 rows) it enters
-0.00000000000001061145. In both cases cell G467 was a number >10, and
correct results were produced for G467 values as small as 0.01. I cannot use
"precision as displayed" because my spreadsheets are often 40MB with
thousands of rows and 100 or more columns. To keep the display manageable, I
minimize the displayed digits. Using ROUND everywhere is a workaround but
significantly increases the size of the spreadsheet, and I have never had to
do it until now. I have also recently noticed a similar problem with an
optimization macro that I wrote using SOLVER. Coding that should set one of
the conditions to a zero value occasionally does not result in a zero, but
again to a very very tiny negative number. This causes havoc when using SORT
to sort the results, because I first have to go through thousands of results
and copy/paste 0.0 into every cell that appears to be a zero, but often is
not a zero. As an independent consulting engineer, I cannot tolerate the
possibility of giving incorrect results to clients, nor can I tolerate the
time taken to manually expand every cell to check for non zero values. Has
anyone an answer please.
received did not help. I have recently begun to experience random problems
with conditional IF formulas not returning zero but a very very tiny negative
number which causes wrong results in other cells which require a zero value
from the IF formula to calculate correctly. I have never experienced this
problem before. It started in Office XP, but I have now upgraded to Office
2003 and the error is still there. An example of the formula is:
=IF(D467>=$D$25,IF(G467>0,0,G467),IF(E467+G467*B467*24*60*60/1000000>$D$26,($D$26-E467)*1000000/(B467*24*60*60),G467))
The error occurs with the first nested IF because it should enter zero when
G467>0, but occasionally (twice in 1000 rows) it enters
-0.00000000000001061145. In both cases cell G467 was a number >10, and
correct results were produced for G467 values as small as 0.01. I cannot use
"precision as displayed" because my spreadsheets are often 40MB with
thousands of rows and 100 or more columns. To keep the display manageable, I
minimize the displayed digits. Using ROUND everywhere is a workaround but
significantly increases the size of the spreadsheet, and I have never had to
do it until now. I have also recently noticed a similar problem with an
optimization macro that I wrote using SOLVER. Coding that should set one of
the conditions to a zero value occasionally does not result in a zero, but
again to a very very tiny negative number. This causes havoc when using SORT
to sort the results, because I first have to go through thousands of results
and copy/paste 0.0 into every cell that appears to be a zero, but often is
not a zero. As an independent consulting engineer, I cannot tolerate the
possibility of giving incorrect results to clients, nor can I tolerate the
time taken to manually expand every cell to check for non zero values. Has
anyone an answer please.