Solved

Accounting Calculation formulas not adding correctly

Posted on 2010-09-23
6
424 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

820 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