Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-29-2020, 12:12 PM
MimiCush MimiCush is offline Excel adding decimal digits Windows 10 Excel adding decimal digits Office 2013
Novice
Excel adding decimal digits
 
Join Date: Jan 2018
Posts: 29
MimiCush is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 04-30-2020, 04:51 AM
p45cal's Avatar
p45cal p45cal is offline Excel adding decimal digits Windows 10 Excel adding decimal digits Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

As ever, attach a workbook with this happening.
Reply With Quote
  #3  
Old 05-01-2020, 03:09 AM
joeu2004 joeu2004 is offline Excel adding decimal digits Windows 7 32bit Excel adding decimal digits Office 2007
Advanced Beginner
 
Join Date: Aug 2016
Posts: 32
joeu2004 is on a distinguished road
Default

Quote:
Originally Posted by MimiCush View Post
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.)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel adding decimal digits 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
Excel adding decimal digits Problems merging in last 4 digits of an account higher than 16 digits Glynda Mail Merge 1 04-08-2011 12:17 AM
Excel adding decimal digits Excel convert format [h]:mm:ss to decimal gchan2000 Excel 1 08-17-2010 01:36 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft