Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Accounting Calculation formulas not adding correctly

Posted on 2010-09-23
6
Medium Priority
?
438 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 2000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

824 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