Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Subtracting decimals
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. |
#2
|
|||
|
|||
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 "Mike A" <Mike wrote in message ... 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. |
#3
|
|||
|
|||
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. In article , Mike A <Mike wrote: 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? |
#4
|
|||
|
|||
It is the way that Excel keeps floating point numbers.
The workaround is to use something like =round(1282.4-1282.39,2) "Mike A" wrote: 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. |
#5
|
|||
|
|||
....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. "Mike A" wrote: 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. |
#6
|
|||
|
|||
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.010000000000000000208166817117216851329430937767 02880859375 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 Mike A wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What formula is used for subtracting a range of different cells f. | Excel Worksheet Functions | |||
change 5523.44 to 552344 no decimals | Excel Worksheet Functions | |||
No decimals and leading zeroes | Excel Discussion (Misc queries) | |||
Decimals separation by . or , | Excel Discussion (Misc queries) | |||
Aligning decimals in Excel ... | Excel Discussion (Misc queries) |