rounding error

  • Thread starter Thread starter Eric Nelson
  • Start date Start date
E

Eric Nelson

Public Function SocialSecurity(GrossPay As Double) As
Double
Dim ss As Double
Dim rss As Double
ss = GrossPay * 0.062
rss = Round(ss, 2)

Called with GrossPay = 7.5
ss = .465
rss = .46 (but should be .47)

What's up??
 
Hey there
Public Function SocialSecurity(GrossPay As Double) As
Double
Dim ss As Double
Dim rss As Double
ss = GrossPay * 0.062
rss = Round(ss, 2)

Called with GrossPay = 7.5
ss = .465
rss = .46 (but should be .47)

What's up??
Click to expand...

Well, this is the nature of floating-point numbers in our modern
IT world. Since computers can only juggle with simple bits,
all data types (such as integers, floating-points etc.) have to be
somehow interpreted into sets of bits. In case of floating-
point numbers (Single and Double in VBA) according to
internationals standards (IEEE), this interpretation is not
an exact one, rounding errors occur. However, in Access
(and VBA), there is a workaround data type for this: Currency.
This is basically a long integer data type (64 bits) which is
divided by 10000 when displayed. This way, all the calculations
can be done without rounding errors (using integer arithmetic), but
the programmer is restricted to 4 usable right-of-comma positions...

In your case, rewrite the function using data type Currency:

Public Function SocialSecurity(GrossPay As Currency) As Currency
Dim ss As Currency
Dim rss As Currency
ss = GrossPay * 0.062
rss = myRound(ss, 2)
...

Note, that you have to supply your own round-function (using
data type currency) or else the round function will still disappoint you
by possibly rounding the result to an unexpected value:

Public Function myRound(v As Currency, pow As Integer) As Currency
myRound = Int(v * 10 ^ pow + 0.5) / 10 ^ pow
End Function



HTH,

Martin
 
Hi Martin, thanks for the tip. I am aware binary fractions
differ from decimal ones. In fact the number .465 is
really 0.46499999999999997, so the behavior is as expected.

I was put off the trail by the fact the Round() function
in Excel behaves as desired, returning .47, not .46.

Eric
 
The VBA Round() function (unlike the Excel ROUND() worksheet function) uses
so-called "Banker's" rounding. In other words, if the next digit after the
specified decimal place is 5 (and there are no other digits after that), it
rounds either up or down, whichever will result in an even numbered digit in
the specified decimal place. For example:

Round(2.345, 2)

returns 2.34

and

Round(2.335, 2)

also returns 2.34 instead of 2.33.

In your case, it rounds 0.465 to 0.46 since the last digit in 0.46 is an
even number.

If you always want to round up, you might use an expression like:

Int(ss * 100 + Sgn(ss) * 0.5) / 100
 
Back
Top