[AccessD] A2K - Adding decimal error

MartyConnelly martyconnelly at shaw.ca
Wed Apr 27 12:14:36 CDT 2005


You will get this error with any series of long computer calculations.
The general way to resolve this,  is to round to the number of 
significant digits desired
 then do each addition or division etc.

When working with money the number of signicant digits  is the the 
smallest unit or 2 for decimal currencies
So you don't have to work this out for calculations.

 Here is a function I use for Rounding Currency,
Your accountant may use different rounding methods simple, banker's, 
synchronous or asynchronous

It takes care of sign problem and allows precision selection
of currency value. Not all currencies are decimal based, old turkish lira

Function RoundIt( _
    x As Currency, _
    Optional Precision As Integer = 2 _
    ) As Currency

    RoundIt = Sgn(x) * Int(CDec(Abs(x)) * 10 ^ Precision _
    + 0.5) / 10 ^ Precision
      Debug.Print RoundIt
End Function

There is an entire branch of mathematics that deals purely with error 
propagation.  It can give you
accurate guesses at what the final accuracy will be of mixed accuracy 
calculations.

ACC2000: Round or Truncate Currency Values to the Intended Number of
Decimals
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210564

essentialy to round via bankers rounding (there are varieties of methods
for this, especially on rounding up or down on negative numbers)
so see this as well.

ACC2000: How to Round a Number Up or Down by a Desired Increment
http://support.microsoft.com/default.aspx?scid=kb;en-us;q209996

Simplest explanation of significant digits and rounding errors
http://www.newton.dep.anl.gov/askasci/math99/math99163.htm

http://www.rit.edu/~uphysics/uncertainties/Uncertaintiespart2.html
http://www.rit.edu/~uphysics/uncertainties/Uncertaintiespart1.html
http://teacher.nsrl.rochester.edu/phy_labs/AppendixB/AppendixB.html


Knuth has a very good analysis of floating point errors


Reuben Cummings wrote:

>A client came across this for me...
>
>One employee has 10 pay records on one day that are each .6 hours.  After
>gathering all the pay info I use a standard grouping query with a sum to get
>the total hours.
>
>Rather than adding up to 6 hours, these 10 records resolve to
>6.00000023841858
>
>Why the strange decimals?
>
>Thanks.
>
>Reuben Cummings
>GFC, LLC
>phone: 812.523.1017
>email: reuben at gfconsultants.com
>
>
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada






More information about the AccessD mailing list