Solved

Accounting Calculation formulas not adding correctly

Posted on 2010-09-23
6
428 Views
Last Modified: 2012-05-10
Attached, please find a simple spreadsheet where if you carry the digits out 10 or more, the balance (which should be zero), isn't zero.  Has anyone seen this before?
Recon.xlsx
0
Comment
Question by:rexxnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 11

Accepted Solution

by:
rowansmith earned 500 total points
ID: 33743273
0
 
LVL 12

Expert Comment

by:ill
ID: 33743296
you may need to change the format of cells in some columns to use more/less decimal places.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33743366
That's not really an Excel bug - it's a computer 'bug' generally. Round the numbers to whatever precision concerns you.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rexxnet
ID: 33743367
Thanks, rowansmith.  We would have never noticed it until the decimals were very extended.  I've not run across this before.

ill:
For small formulas, the variance may be insignificant.  However, this is a company that forecasts forest data with millions of cell data.  The small error would make a significant difference.

Either of you have any idea how to fix this issue or if there is a patch out there?  I cannot find one.  
0
 
LVL 11

Expert Comment

by:rowansmith
ID: 33743525
It is a bug and it isn't a bug.  It depends on your perspective.  If it's not doing what you expect then it's a bug.  For me I expect this and so it is not a bug :-)

The problem is unavoidable in the sense that there are decimal numbers which have no exact, finite representation in binary but they have to be stored with a limited number of digits.

The problem is that the binary representation of the decimal 0.1 is 0.0001100110011... where the 0011 repeats forever.  The floating point representation of 0.1 is inherently inaccurate.  In a more familiar setting, if we had a machine that could store a two decimal digit mantissa, then 1/3 would be stored as .33 and when added to itself two times would yield .99 rather than 1.0.  Displayed values are often rounded so that the last couple of digits are not displayed so that the underlying representation is again different from the displayed value. [REF: http://www.lawrence.edu/fast/jordanj/FloatingPoint.html]

To avoid seeing it, you need to use Excel's "ROUND" function to limit your numbers to some significant number of digits (say 10 or 12).  The other solution is to use something, like APL or J (see www.jsoftware.com) with the notion of comparison tolerance built in. [REF: http://www.lawrence.edu/fast/jordanj/FloatingPoint.html]

-Rowan
0
 

Author Closing Comment

by:rexxnet
ID: 33743564
The explaination is clear and a potential solution has been presented for this type of formula.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question