[AccessD] A2K - Adding decimal error

Heenan, Lambert Lambert.Heenan at AIG.com
Thu Apr 28 09:17:42 CDT 2005


What surprises me about this thread is that people are surprised that FP
numbers are subject to these rounding problems. I thought that was just a
simple fact of life due to the binary nature of computer data and it was
covered in CS101. :-)

One other thing surprised me - that Gustav's routine gets though as many as
five iterations before it finds a difference between fSum and fSuma!  Two or
three would have been my guess.

Lambert

> -----Original Message-----
> From:	accessd-bounces at databaseadvisors.com
> [SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent:	Thursday, April 28, 2005 4:36 AM
> To:	accessd at databaseadvisors.com
> Subject:	Re: [AccessD] A2K - Adding decimal error
> 
> Hi Marty
> 
> That's right, but Charlotte mentioned adding 1 not 0.01.
> 
> However, if anyone at this point still are confident using Double (or <
> gosh> Single) for decimals, I modified your test.
> This is scary. Notes follow the function:
> 
> Sub CalcSum()
>   
>   ' This will take a fraction of a second to run.
>   ' Add 1 cent up to 1 billion times and apply rounding.
>   ' Exit at first encountered error.
>   
>   Dim i As Long
>   Dim iMax   As Long
>   Dim fSum  As Double
>   Dim fSuma  As Double
>   Dim fPenny As Double
>   i = 0
>   fPenny = 0.01
>   iMax = 1000000000
>   fSum = 0#
>   fSuma = 0#
>   For i = 0 To iMax - 1
>     fSum = fSum + fPenny
>     fSuma = fSuma + fPenny
>     'intermediate round to 2 decimal point precision
>     fSuma = Round(fSuma, 2)
>     ' Exit if an error has occurred.
>     If fSum <> fSuma Then Exit For
>   Next i
>   
>   Debug.Print fSum; fSuma, fSum - fSuma
> 
> End Sub
> 
> Now, take your guess - how many loops will run? Ten thousand? 
> You'll be in for a surprise!
> 
> Note that even though fSum and fSuma display the same result, they
> don't compare.
> You have to do the fSum - fSuma subtraction to have the error
> displayed.
> 
> /gustav
> 
> >>> martyconnelly at shaw.ca 04/27 10:32 pm >>>
> Don't forget you must round to the precision needed in the
> intermediate
> calculations to avoid bias or roundoff errors, regardless of the
> precision
> of the variables used or compiler.
> After doing one billion additions of .01 or say 1 cent
> The difference in calculated values can be seen when you apply the
> intermediate rounding function.
> Applying this method will keep demented accountants out of your hair.
> 
> Final Total unrounded decimal     accurate  intermediate rounded total
>  9999999.82515867                               10000000
> 
>  Sub CalcSum()
>   'this may take several minutes to run
>   ' add 1 cent 1 billion times and apply rounding
>                         Dim i As Long
>                         Dim iMax   As Long
>                         Dim fSum  As Double
>                         Dim fSuma  As Double
>                         Dim fPenny As Double
>                         i = 0
>                         fPenny = 0.01
>                         iMax = 1000000000
>                         fSum = 0#
>                         fSuma = 0#
>                         For i = 0 To iMax - 1
> 
>                            fSum = fSum + fPenny
>                            fSuma = fSuma + fPenny
>                            'intermediate round to 2 decimal point
> precision
>                            fSuma = Round(fSuma, 2)
>                         Next i
> Debug.Print fSum; fSuma
>  End Sub
> 
> 
> Gustav Brock wrote:
> 
> >Hi Charlotte
> >
> >It fails all right, but not you have to go beyond 16 mio.:
> >
> >Public Sub TestSingle()
> >
> >  Dim sng As Single
> >  Dim lng As Long
> >  
> >  For lng = 0 To 100000000
> >    If lng <> sng Then Exit For
> >    sng = sng + 1
> >  Next
> >  Debug.Print lng, sng
> >
> >End Sub
> >
> >It runs to:
> > 16777217      1.677722E+07 
> >
> >/gustav
> >
> >  
> >
> >>>>cfoust at infostatsystems.com 04/27 7:17 pm >>>
> >>>>        
> >>>>
> >I've seen it with addition as well.  You can build a test in code and
> >loop multiple times adding 1 to the integer of a floating point and
> >testing the mod to see if there is a remainder.  Believe me, it will
> >occur.
> >
> >Charlotte Foust
> >
> >
> >-----Original Message-----
> >From: Gustav Brock [mailto:Gustav at cactus.dk] 
> >Sent: Wednesday, April 27, 2005 9:37 AM
> >To: accessd at databaseadvisors.com 
> >Subject: RE: [AccessD] A2K - Adding decimal error
> >
> >
> >Hi Reuben
> >
> >Charlotte a guy? You better be careful!
> >
> >You mostly see this with subtraction (which includes addition of
> >negative values); I've never seen it with addition of positives.
> >
> >If you can't change the source data type be sure to wrap the values
> in
> >CCur(). If you need more than the four decimals of Currency you may
> >choose Double but round the results whenever possible.
> >
> >/gustav
> >
> >  
> >
> >>>>reuben at gfconsultants.com 04/27 6:26 pm >>>
> >>>>        
> >>>>
> >You guys nailed that one.  HoursWorked is a single.  I never
> >considered it would cause a problem with addition.  Of course, after
> all the work
> >I did on payroll calculations I can't beleive it was still a single.
> >
> >Thanks.
> >
> >Reuben Cummings
> >GFC, LLC
> >phone: 812.523.1017
> >email: reuben at gfconsultants.com 
> >
> >
> >-----Original Message-----
> >From: accessd-bounces at databaseadvisors.com 
> >[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav
> Brock
> >Sent: Wednesday, April 27, 2005 10:53 AM
> >To: accessd at databaseadvisors.com 
> >Subject: Re: [AccessD] A2K - Adding decimal error
> >
> >
> >Hi Reuben
> >
> >He is probably adding and subtraction 0.1 hour as well and you are
> >using
> >data type Single for that. Change to data type Currency ...
> >
> >/gustav
> >
> >  
> >
> >>>>reuben at gfconsultants.com 04/27 5:38 pm >>>
> >>>>        
> >>>>
> >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 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



More information about the AccessD mailing list