Solved

Accounting Calculation formulas not adding correctly

Posted on 2010-09-23
6
422 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
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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