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