Round Function

[Joshua A. Booker]

Hi,

Why does Round(34.385,2) return 34.38?
Shouldn't it return 34.39?

TIA,
Josh

 

[John Spencer (MVP)]

Access round functions uses banker's rounding.

Basically that means that if the next significant digit is a 5, then Access will
round to the nearest EVEN number.

 

[Yuan Shao]

Hi Josh,

Thanks for your post. The Round function that we are using is VBA function.
This Round function introduced in Microsoft Visual Basic for Applications
version 6.0 is called "Banker's Rounding" while the general round function
is called "Arithmetic Rounding

Unfortunately Visual Basic for Applications does not have any function that
does arithmetic rounding. To work around this problem, we can create a
custom Rounding function to replace the built-in Round() function:

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function

The details of this issue are mentioned in the following article:
http://support.microsoft.com/?id=196652

You may also take a look at the following article:
http://support.microsoft.com/?id=225330

Please feel free to post in the group if this solves your problem or if you
would like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Mike Sherrill]

Why does Round(34.385,2) return 34.38?
Shouldn't it return 34.39?

In elementary school, I learned there was only one right answer to the
question "What is 3.445 rounded to two decimal places?" Nowadays, I
know better. : )

What you're seeing--3.445 rounding to 3.44--is "round to nearest even"
behavior. Numbers on the cusp round to the nearest even number. (I
understand that some applications round to the nearest odd number, but
I haven't seen any of them firsthand.)

Here's why you might want to do such a seemingly silly thing. In
rounding to two decimal places . . .

3.440 <truncated>
3.441 <round down>
3.442 <round down>
3.443 <round down>
3.444 <round down>
3.445 <???>
3.446 <round up>
3.447 <round up>
3.448 <round up>
3.449 <round up>
3.450 <truncated>

Four intermediate values round to 3.44, and four values round to 3.45.
If you *always* round 3.445 in one direction, you introduce a bias:
you round four intermediate values in one direction, and *five* values
in the other.

To minimize this kind of bias, round n.nn5 up half the time and down
half the time. The simplest way to do that is to round to the nearest
even number. That's what Access usually does. (Format() rounds more
or less the way I learned in elementary school.)