INT and TRUNC functions don't seem to work correctly inside IF

[OzGary]

Hi,

I am trying to construct a spreadsheet that calculates what notes/coin to give as change.
Under a specific set of calculations, even though both INT and TRUNC functions return a "1" result, when nested into an IF function they return a zero.
In the last entry line of the attached workbook, everything is fine until you enter $500 as the amount tendered. The formula in cell O4 then falls over and I get a zero result, even though the nested TRUNC function has a result of 1.
What am I doing wrong?
Regards,
Gary

[kersplash]

Try using Formulas -> Evaluate Formula. This shows that the result of the TRUNC(76.25-76.2) is actually 0.04999999999999972 not 0.05 as you probably expect.
https://blogs.office.com/en-us/2008/...wrong-answers/

[OzGary]

Thank you kersplash.
I wasn't aware of that floating point precision problem - thanks for directing me to that blog.
I have used the ROUND function inside the TRUNC, and all is sweet.
You've saved my sanity!
Gary

[joeu2004]

I wasn't aware of that floating point precision problem - thanks for directing me to that blog

.... Which is wrong in detail, as is common for these explanantions even from MSFT.

In particular, it is not true that "Excel store[s] 15 significant digits of precision".

However, it is true that Excel displays (formats) only up to 15 significant digits (rounded).

And some for operations (including TRUNC and INT), Excel converts the exact internal value (binary) to its 15-significant-digit representaton first.

For example, =76.25-76.2=0.0499999999999972 returns TRUE, but =76.25-76.2-0.0499999999999972=0 returns FALSE(!).

The reason is: the "=" operator compares values rounded to 15 significant digits, but 76.25-76.2-0.0499999999999972 in that context is evaluated to its exact binary value, about -4.16E-17.

And that difference demonstrates that Excel does not "store" only 15 significant digits.

Ironically, in this case, =76.25-76.2-0.0499999999999972 returns exactly zero (0.00E+00 when formatted as Scientific) because Excel sometimes and inconsistently arbitrarily replaces the exact arithmetic result with zero under very specific conditions.

In contrast, =76.25-76.2-0.0499999999999972-0 and =(76.25-76.2-0.0499999999999972) return about -4.16E-17 because they do not meet the specific conditions for the arbitrary substitution.

Not for nothing, just when you thought your "sanity" was saved. (wink)

But explicit rounding is indeed the best method for avoiding these anomalies (except for a defect in Excel (sigh), which appears very rarely). And round to the precision that you expect a calculation to be accurate to, not an arbitrary number of digits like 10, as some people suggest.