Rounding

  • Thread starter Thread starter DB
  • Start date Start date
D

DB

Hi, I'm using the "round" function in a query like this:
Round([Field_Name],0). My problem is that when a number
has a decimal of exactly .5 it gets rounded down and I
would like it to be rounded up. For example, when 10.5 is
returned it gets rounded to 10. I would like it to be
rounded to 11. Is it possible to have a decimal of .5 be
rounded up instead of down? Thanks.
 
I'm not sure how "round" works, but you don't actually
need it to round to the nearest integer. A well-known
method of rounding a number off to the nearest integer is
int( [Field_Name] + .5 ). This works because if the
decimal part is greater than or equal to .5, adding .5 to
the value will bump up the integer part by one (10.5 + .5
= 11, 10.6 + .5 = 11.1, etc). However, if the decimal
part is less than .5, the integer part remains the same
(10.4 + .5 = 10.9, etc). The int() function then takes
[field_name] + .5 and returns only the integer part,
effectively rounding [field_name] to the nearest integer.
This should round 10.5 up to 11.
 
Actually, I think you'll find 10.5 gets rounded down, and 11.5 gets rounded
up. At least that's the way it's supposed to work in Access: it's called
Banker's Rounding, and it's supposed to minimize roundoff error when you're
dealing with a number of records.

Take a look at http://support.microsoft.com/?id=196652 for a very detailed
discussion, complete with sample code to support a variety of rounding
approaches.
 
Thanks, works great.
-----Original Message-----
I'm not sure how "round" works, but you don't actually
need it to round to the nearest integer. A well-known
method of rounding a number off to the nearest integer is
int( [Field_Name] + .5 ). This works because if the
decimal part is greater than or equal to .5, adding .5 to
the value will bump up the integer part by one (10.5 + .5
= 11, 10.6 + .5 = 11.1, etc). However, if the decimal
part is less than .5, the integer part remains the same
(10.4 + .5 = 10.9, etc). The int() function then takes
[field_name] + .5 and returns only the integer part,
effectively rounding [field_name] to the nearest integer.
This should round 10.5 up to 11.
-----Original Message-----
Hi, I'm using the "round" function in a query like this:
Round([Field_Name],0). My problem is that when a number
has a decimal of exactly .5 it gets rounded down and I
would like it to be rounded up. For example, when 10.5 is
returned it gets rounded to 10. I would like it to be
rounded to 11. Is it possible to have a decimal of .5 be
rounded up instead of down? Thanks.
.
Click to expand...
.
Click to expand...
 
Thanks for the article.
-----Original Message-----
Actually, I think you'll find 10.5 gets rounded down, and 11.5 gets rounded
up. At least that's the way it's supposed to work in Access: it's called
Banker's Rounding, and it's supposed to minimize roundoff error when you're
dealing with a number of records.

Take a look at http://support.microsoft.com/?id=196652 for a very detailed
discussion, complete with sample code to support a variety of rounding
approaches.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Hi, I'm using the "round" function in a query like this:
Round([Field_Name],0). My problem is that when a number
has a decimal of exactly .5 it gets rounded down and I
would like it to be rounded up. For example, when 10.5 is
returned it gets rounded to 10. I would like it to be
rounded to 11. Is it possible to have a decimal of .5 be
rounded up instead of down? Thanks.
Click to expand...


.
Click to expand...
 
Douglas said:
Actually, I think you'll find 10.5 gets rounded down, and 11.5 gets
rounded up. At least that's the way it's supposed to work in Access:
it's called Banker's Rounding, and it's supposed to minimize roundoff
error when you're dealing with a number of records.

Take a look at http://support.microsoft.com/?id=196652 for a very
detailed discussion, complete with sample code to support a variety
of rounding approaches.
Click to expand...
Computers are a "bit" faster than they were 20 years ago. I wish somebody
would move back to Binary Coded Decimal for calculations and get rid of
round off and truncation errors.
 
Back
Top