Subtracting decimals

[Guest]

Why is this? ...
In Excel if I subtract, for example, 1282.39 from 1282.40, and the answer
cell is displayed with 14 or more decimal places, the result displays as
0.00999999999999, when in fact the correct result, obviously, is exactly
0.01. What's happening here, and how do I prevent it?
Thanks.

 

[N Harkawat]

one of the 2 values that you are subtracting is not exactly a 2 decimal
place numeral
first make them such by using the round function and then when you subtract
you should see 0.01

Conversely you could round the result:
=round(b1-a1,2)
where b1 = 1282.4
and a1 = 1282.39

 

[JE McGimpsey]

What's happening is an inherent limitation in IEEE Double Precision
Floating Point math (which nearly all spreadsheets use):

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


To prevent it, you can use explicit rounding:

=ROUND(1282.4-1282.39,2)

or you can use the global Tools/Options/Calculation/Precision as
Displayed, which will truncate all your values to what is displayed.

 

[Guest]

It is the way that Excel keeps floating point numbers.

The workaround is to use something like
=round(1282.4-1282.39,2)

 

[Guest]

....just format the cells (or the entire sheet) to "Number" and select "2"
decimal places. Select the entire sheet by clicking the button between
column A and row 1, or highlight specific cells, then right click and select
"Format Cells". Go to the "Number" tab, select number and choose your
decimal places.

 

[Jerry W. Lewis]

Neither .39 nor .4 (like most other decimal fractions) can be exactly
represented in binary (just as 1/3 cannot be exactly represented in
decimal). Excel (and almost all other computer software) follows the
IEEE standard for double precision storage of numbers, which defines
where to cut off the infinite binary representation of these numbers.
The resulting approximations are
1282.40000000000009094947017729282379150390625
and
1282.390000000000100044417195022106170654296875
The exact difference of these approximate inputs is
0.009999999999990905052982270717620849609375
which Excel correctly displays to its documented limit of 15 decimal digits.

Your options are to either round results appropriately, accept the
approximate output resulting from approximations to your inputs, or
restructure to integer equations, which are exactly representable (
=128240-128239 will return 1 as expected, because the inputs are exactly
representable).

Given your equation, anything beyond the 2nd decimal place is the result
of binary approximations to the inputs, so you can round to 2 decimal
places which will give the binary approximation to 0.01 (also not
exactly representable)
0.01000000000000000020816681711721685132943093776702880859375
which Excel will display to its documented limit of 15 decimal digits as
0.01.

You can easily use that documented limit (15 decimal digits) to predict
the magnitude of approximation effects. Think of your equation as
1282.40000000000???
-1282.39000000000???
-------------------
0.01000000000???
which is consistent with Excel's result of
0.00999999999999091

Jerry