Quote:
Originally Posted by MimiCush
One column is calculated by subtracting two 2-decimal columns from another 2-decimal column. Each of those columns are typed in and there is no odd data.
|
This is a common problem in Excel and other (most) applications that use 64-bit binary floating-point to represent numeric values internally.
In general, when you want a calculation with non-integers to be accurate to some number of decimal places, explicitly round the calculation to that number of decimal places. (Not to an arbitrary number of decimal places like 10, as some people suggest.)
For example, =10.01-10 does not equal 0.01 (!). Instead, it is about 0.00999999999999979.
The remedy: =ROUND(10.01-10, 2)
In your example, it appears that you sum a column of numbers. If your formula is =SUM(A1:A16), you should change it to =ROUND(SUM(A1:A16), 2).
-----
The problem arises because most decimal fractions cannot be represented exactly in 64-bit binary floating-point. And the binary representation of a particular decimal fraction might vary with the magnitude of the value.
For example, the exact decimal representation is on the right.
0.01: 0.0100000000000000,0020816681711721685132943093776 702880859375
10.01: 10.0099999999999,997868371792719699442386627197265 625
10.01-10: 0.00999999999999978,683717927196994423866271972656 25
(I use period for the decimal place and comma to demarcate the first 15 significant digits, which is all that Excel formats, rounded.)