?
Solved

Accounting Calculation formulas not adding correctly

Posted on 2010-09-23
6
Medium Priority
?
441 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

584 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