Problems with INT()

[Andy B]

Hi all

Whilst in the process of replying to someone's post, I discovered a problem
with using INT(). (All cells are formatted as General.)

In A1 I have 32.51
In B1 I have =A1-INT(A1) this gives me 0.51
In C1 I have =B1-0.51 this gives me -1.99840144432528E-15

It doesn't give zero, whichever value I use in A1

Why is this?

Andy.

 

[Norman Harker]

Hi Andy!

Excel is commonly reported as getting its maths all wrong with results
that should be equal being treated as not equal.



See the Microsoft Knowledge base articles for full coverage of
difficulties that Excel and all computing programs have with
apparently simple calculations.



It’s a Binary Numbers ‘thing’ and computers all use binary numbers.
You’ll find it covered in various Microsoft Knowledge base articles:

78113 XL: Floating-Point Arithmetic May Give Inaccurate Results

http://support.microsoft.com/default.aspx?scid=kb;EN-US;78113

214118 XL: How to Correct Rounding Errors in Floating-Point Arithmetic

http://support.microsoft.com/default.aspx?scid=kb;EN-US;214118

172911 XL: Incorrect Result Raising 10 to Very Large/Very Small Power

http://support.microsoft.com/default.aspx?scid=kb;EN-US;172911

214373 XL2000: Incorrect Result Raising 10 to Very Large/Very Small
Power

http://support.microsoft.com/default.aspx?scid=kb;EN-US;214373

42980 (Complete) Tutorial to Understand IEEE Floating-Point Errors

http://support.microsoft.com/default.aspx?scid=KB;en-us;42980

Chip Pearson covers the issue at:

http://www.cpearson.com/excel/rounding.htm


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Andy B]

Cheers!! Plenty to go at there, then.

Andy.