#1
|
|||
|
|||
Excel adding decimal digits
I use Excel 2013. I have a table with 2000 rows. 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.
There are only 16 examples of this, but it is weird - this is what the column holds for those rows: Amount Due $(0.0000000000104773793807578000) $(0.0000000000072759576141834300) $(0.0000000000036379788070917100) $(0.0013592233008239400000000000) $(0.0021359223301260500000000000) $(0.0000000000006821210263296960) $(0.0029999999956089600000000000) $(0.0000000000291038304567337000) $(0.0000000000072759576141834300) $(0.0000000000072759576141834300) $(0.0000000000072759576141834300) $(0.0000000000072759576141834300) $(0.0000000000011652900866465600) $(0.0000000000372892827726901000) $(0.0000000000000248689957516035) $(0.0000000000003979039320256560) $(0.01) this is a subtotal row Any idea why this happened? |
#2
|
||||
|
||||
As ever, attach a workbook with this happening.
|
#3
|
|||
|
|||
Quote:
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.) |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding a decimal point in an already existing column of numbers | wheddingsjr | Excel | 3 | 07-26-2019 07:41 AM |
How to create a decimal point after each number in a numbered list in excel | wondermuse | Excel | 1 | 03-06-2017 03:42 AM |
Pulling 2 digits before a decimal point from adjoining cell then zeros after decimal | jadess916 | Excel | 1 | 06-26-2014 03:48 AM |
Problems merging in last 4 digits of an account higher than 16 digits | Glynda | Mail Merge | 1 | 04-08-2011 12:17 AM |
Excel convert format [h]:mm:ss to decimal | gchan2000 | Excel | 1 | 08-17-2010 01:36 PM |