Excel 2003 IF formula returns non-zero value instead of zero

[Guest]

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 rely on a zero value from the IF
formula to calculate correctly. In a reservoir routing speadsheet with say
2000 rows of calculations and 1000 that should be zero, two are not, but
tracing them can take hours expanding every cell display to 15 or more
decimal places to find which are not zero. I have never had this problem
before. It started in Office XP, but today I upgraded to Office 2003 and the
error is still there. Using ROUND everywhere is a work around but
significantly increases the size of the spreadsheet. Does anyone know the
cause and solution?

 

[Pete_UK]

Could you post an example of one of the formulae giving rise to this
problem?

Pete

 

[Guest]

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 it enters -0.00000000000001061145

 

[Jim Rech]

I deal with these problems by setting Precision As Displayed on under Tools,
Options, Calculation. Any chance you had it on and turned it off?

--
Jim
| 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 it enters -0.00000000000001061145
| --
| Hydrotechchris
|
|
| "Pete_UK" wrote:
|
| > Could you post an example of one of the formulae giving rise to this
| > problem?
| >
| > Pete
| >
| > On Jul 26, 7:50 pm, HydrotechChris
| > > 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 rely on a zero value from
the IF
| > > formula to calculate correctly. In a reservoir routing speadsheet
with say
| > > 2000 rows of calculations and 1000 that should be zero, two are not,
but
| > > tracing them can take hours expanding every cell display to 15 or more
| > > decimal places to find which are not zero. I have never had this
problem
| > > before. It started in Office XP, but today I upgraded to Office 2003
and the
| > > error is still there. Using ROUND everywhere is a work around but
| > > significantly increases the size of the spreadsheet. Does anyone know
the
| > > cause and solution?
| > > --
| > > Hydrotechchris
| >
| >
| >

 

[Guest]

Hi Jim,

I have never used Precision As Displayed because my spreadsheets are often
in the order of 40MB with thousands of rows and 100 columns or more. To keep
the display manageable I minimize the digits displayed, and I cannot accept
the inaccuracy that would result if everything was rounded to the displayed
precision.

 

[Guest]

Hi Pete,

I don't know if it helps, but I also noticed the same issue occurring in an
optimization macro that I wrote using Solver based on an example included in
the Excel add-in. Coding that should have set one of the conditions to zero
did not result in a zero, but a very very tiny negative value, which produced
major problems when I used Sort to sort the results, because I first had to
go through thousands of results and copy/paste 0.0 into every cell that
appeared to be zero, but often wasn't zero.

 

[Pete_UK]

Hi Chris,

I think your problem is not specifically the first nested IF, i.e.:

=IF(D467>=$D$25,IF(G467>0,0,G467), ...

but the formula which is in G467 - such a small value as
-0.00000000000001061145 is still not greater than 0, and so this value
will be returned.

You could perhaps change the comparator to >= rather than just >, and
Excel might treat this small number as being close enough to zero, but
I think you need to make the adjustment to the formula in G467 to
ensure that such small numbers are treated as zero. Only you know what
accuracy and precision is required for the rest of the sheet.

Hope this helps.

Pete

 

[Guest]

Hi Pete,

In both cases of non-zero results, the value in the column G cell is a large
number, i.e. 14.95 and 37.45, whereas other rows return the correct zero
result when the G cell is as small as 0.01.