Extremely Weird Math Bug !! Formula is correct, but Excel aggregates wrong!!

[superlative]

Please see attached spreadsheet, showing the mathematical error. You'll see in Column B is a repeating formula that calculates an integer number that changes every 14 days on a Tuesday.

The problem is in line 47, where suddenly thereafter it changes the integer number every Monday!!

If you dissect the formula:

=IF(ABS(B46-TRUNC(B46)) + ((A47-A46)*0.01) > 0.01*$C$4, TRUNC(B46)+1.01, B46 + ((A47-A46)*0.01))

The blue formula calculates as FALSE, and yet the TRUE part of the IF statement (red) is executed!!!

Does anyone know why? Thanks!!!

[Olly]

Why is it happening: floating point arithmetic (read more here) is causing the initial criteria to evaluate TRUE.


How did I find that out: Formula > Formula Auditing > Evaluate Formula > Click Evaluate to work through exactly what the formula is doing.

Capture.PNG


How to fix the problem: Introduce a ROUND function:

Please Login or Register  to view this content.

[superlative]

wow!! thanks!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.