By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

1.625 should round to 1.63... not 1.62... RIGHT?

n8kindt
100+
P: 221
using access 2007

here's the code from the builder i'm working with

ExpectedTax: ([TaxPercent]*([Subtotal]-[ProductPurchased]))+(2*[ProductPurchased]*[TaxPercent])

when i was trying this out, one of the results i got was 1.625 ... i need this (and the underlying data) to be rounded to 1.63 ... unfortunately when i tried the round function

ExpectedTax: Round(([TaxPercent]*([Subtotal]-[ProductPurchased]))+(2*[ProductPurchased]*[TaxPercent]),2)

it displays 1.62 ... why doesn't it round up?? i also tried changing to to currency but that does no good b/c the underlying data still displays 1.625 and i need that figure to be rounded at some point. but whatever i do it rounds down.
can anyone help??
Mar 13 '08 #1
Share this Question
Share on Google+
7 Replies


Scott Price
Expert 100+
P: 1,384
The Round function in VBA behaves a little oddly... It utilizes what is called round-to-even or Bankers Rounding. What that means is that it will round to the nearest even number. Rather a nice M$ trick, I'd say ;-)

One way to get around this and make it round as you wish is to add 0.000000001 to the end of your 1.625 figure. This will make it round to the 1.63 figure you want. If adding this will significantly affect your calculations (which I doubt as you are calculating using a rounded number to begin with), you will need to write a special function to handle the rounding.

Regards,
Scott
Mar 14 '08 #2

Scott Price
Expert 100+
P: 1,384
I should clarify my statement... When the decimal number ends with 5 it rounds to the nearest even number. Thus Round(1.625,2) rounds to 1.62, however Round(1.635,2) will result in 1.64.

Adding the 0.00000001 figure makes the decimal end in 1 instead of 5, which makes the rounding work in the traditional way.

Regards,
Scott
Mar 14 '08 #3

n8kindt
100+
P: 221
The Round function in VBA behaves a little oddly... It utilizes what is called round-to-even or Bankers Rounding. What that means is that it will round to the nearest even number. Rather a nice M$ trick, I'd say ;-)

One way to get around this and make it round as you wish is to add 0.000000001 to the end of your 1.625 figure. This will make it round to the 1.63 figure you want. If adding this will significantly affect your calculations (which I doubt as you are calculating using a rounded number to begin with), you will need to write a special function to handle the rounding.

Regards,
Scott
thanks for the explanation, scott. i'll give it a try!
Mar 14 '08 #4

n8kindt
100+
P: 221
I should clarify my statement... When the decimal number ends with 5 it rounds to the nearest even number. Thus Round(1.625,2) rounds to 1.62, however Round(1.635,2) will result in 1.64.

Adding the 0.00000001 figure makes the decimal end in 1 instead of 5, which makes the rounding work in the traditional way.

Regards,
Scott
it seems to have done the trick. thanks so much! i read something about adding the .000001 somewhere else but it didnt make sense without your explanation!

cheers,
nate
Mar 14 '08 #5

Scott Price
Expert 100+
P: 1,384
Not a problem, hope you get it working correctly :-)

Regards,
Scott
Mar 14 '08 #6

Scott Price
Expert 100+
P: 1,384
An interesting article for further reading for you or anyone who stumbles onto this article in the future: http://support.microsoft.com/kb/196652/EN-US/.

The point made about Banker's Rounding and the logic behind it is that consistently rounding decimals ending in 5 up will introduce a bias into your calculations as you add more rounded numbers. In other words, it will artificially inflate your end totals after a certain number of calculations. Banker's Rounding therefore attempts to flatten out this bias curve by introducing a separate rounding algorithm that will keep some of this artificial inflation from happening.

Maybe I should apologize to Bill for my slightly sarcastic comment earlier about M$? :-)

Regards,
Scott
Mar 14 '08 #7

P: n/a
Try to do it this way.. simple.. design view of your table, your number field, format -> decimal and change scale from 0 to 2 and your will be okay
Oct 21 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.