Incorrect Rounding - Any advice

M

Melissa

If you put a text box in any form or report and enter
=Round((5/4),1) you will get 1.2. We all can see the
actual answer is 1.25 therefore it should round to 1.3,
but it doesn't. Has anyone found a workaround for this? I
have a report with about a hundred calculations in it -
that is currently unusable because things are not always
rounding correctly.

Thanks,
Melissa
 
A

Allen Browne

The built-in rounding rounds towards the nearest even number (called
banker's rounding), so 1.25 rounds down towards 1.2, but 1.35 round up
towards 1.4.

If you do not want that, you can create your own rounding function. There is
one by Ken Getz you can copy in this link:
http://www.mvps.org/access/modules/mdl0054.htm

Be sure to rename the function if you are using it in Access 2000 or later,
so it does not fight with the inbuilt Round() function.
 
W

Wayne Morgan

It is rounding "correctly" (i.e. as designed). There is more than one way to
round. While most folks round up at .5, this causes an imbalance (5 numbers,
5-9 round up and 4 numbers, 1-4 round down). So there is another type of
rounding commonly known as "bankers rounding" or "scientific rounding" that
rounds .5 to the nearest even number. This way, you round up half the time
and round down half the time, reducing the skewing of your data. However, it
only rounds .5 this way. If there are additional decimal places such as
1.2500001, this is greater than .5 and will round to 1.3. You have to be
exactly at .5 to round to the nearest even number.

To get the more conventional rounding, you need to create your own function.
Give it a different name, such as MyRound.

Example:
Public Function MyRound(dblNumberToRound As Double, intRoundTo As Integer)
As Double
Dim dblResult As Double, intFactor As Integer
intFactor = 10 ^ (-intRoundTo)
dblResult = dblNumberToRound * intFactor
dblResult = Int(dblResult + 0.5)
MyRound = dblResult / intFactor
End Function

The math is done in more than one step to reduce floating point errors that
will give an undesired result. Using the value you mentioned, this would be
called as

=MyRound((5/4),-1)
 
D

Danny

Thank you Wayne Morgan. Your adviced and function were just what I needed. I do find it wild that Access and Excel round differently. Causes issues when processing things for people who are used to the Excel rounding 5up less than 5 down.
 
Top