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


"Pete_UK" wrote:

Hi Chris,

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

=IF(D467=$D$25,IF(G4670,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




On Jul 26, 9:00 pm, HydrotechChris
wrote:
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.
--
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
wrote:
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- Hide quoted text -

- Show quoted text -