Rounding Question

  • Thread starter Thread starter BobV
  • Start date Start date
B

BobV

Group:

Why doesn't the round function round a number up to the next higher digit
when the significant number is 5 or greater?

For example:

ROUND(454.545,2) = 454.54 instead of 454.55

I would expect 454.545 to be rounded up to 454.55. Since the ROUND function
doesn't do what I want, how do I get it to round up when the significant
number is 5 or higher?

Thanks,
BobV
 
BobV said:
Group:

Why doesn't the round function round a number up to the next higher digit
when the significant number is 5 or greater?

For example:

ROUND(454.545,2) = 454.54 instead of 454.55

I would expect 454.545 to be rounded up to 454.55. Since the ROUND function
doesn't do what I want, how do I get it to round up when the significant
number is 5 or higher?
Click to expand...

Access uses "Banker's Rounding" wherein "fives" are rounded toward the nearest
even value. The reasoning is that these numbers are rounded up approximately
half the time and rounded down approximately half the time. In a large column
of numbers being rounded this means that the inaccuracy introduced by the
rounding tends to cancel out and the final result is more accurate. If you
always round the fives up then your error grows larger and larger as the number
of values being rounded goes up.

If you do a Google search you will find numerous examples of custom rounding
functions where this behavior can be controlled by you.
 
Rick:

Thanks for your explanation. Now I understand why Access rounds the way it
does.

I think I will add .001 to all numbers before using the ROUND function, and
that should round the numbers up the way I want them to be rounded when the
significant digit is 5 or higher.

Thanks Again,
BobV
 
I think I will add .001 to all numbers before using the ROUND
function, and that should round the numbers up the way I want them to
be rounded when the significant digit is 5 or higher.
Click to expand...

There's an easier way to do it, by using the normal INT function. Note that
if x is permitted to be less than zero, you have to make up your own mind
whether you want to round up or away from zero, so check the help files for
FIX() function to see you prefer its behaviour.

IIF(INT(x)=x, x, INT(x+0.5))

For myself, I never understood the rather schlocky behaviour of round,
which to me proves that 'puter systems are just not being put together by
mathematicians any more. :-(


Hope that helps


Tim F
 
Back
Top