Microsoft Office Forums Excel adding decimal digits
 Register FAQ Search Today's Posts Mark Forums Read

#1
04-29-2020, 12:12 PM
 MimiCush Windows 10 Office 2013 Novice Join Date: Jan 2018 Posts: 28

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
04-30-2020, 04:51 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 392

As ever, attach a workbook with this happening.
#3
05-01-2020, 03:09 AM
 joeu2004 Windows 7 32bit Office 2007 Novice Join Date: Aug 2016 Posts: 23

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.)

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post wheddingsjr Excel 3 07-26-2019 07:41 AM wondermuse Excel 1 03-06-2017 03:42 AM jadess916 Excel 1 06-26-2014 03:48 AM Glynda Mail Merge 1 04-08-2011 12:17 AM gchan2000 Excel 1 08-17-2010 01:36 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:21 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top