#1




Excel adding decimal digits
I use Excel 2013. I have a table with 2000 rows. One column is calculated by subtracting two 2decimal columns from another 2decimal 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 nonintegers 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.0110 does not equal 0.01 (!). Instead, it is about 0.00999999999999979. The remedy: =ROUND(10.0110, 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 64bit binary floatingpoint. 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.0110: 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  07262019 07:41 AM 
How to create a decimal point after each number in a numbered list in excel  wondermuse  Excel  1  03062017 03:42 AM 
Pulling 2 digits before a decimal point from adjoining cell then zeros after decimal  jadess916  Excel  1  06262014 03:48 AM 
Problems merging in last 4 digits of an account higher than 16 digits  Glynda  Mail Merge  1  04082011 12:17 AM 
Excel convert format [h]:mm:ss to decimal  gchan2000  Excel  1  08172010 01:36 PM 