1.625 should round to 1.63... not 1.62... RIGHT?  | Familiar Sight | | Join Date: Mar 2008 Location: Southern California
Posts: 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??
|  | Moderator | | Join Date: Jul 2007 Location: Seattle, WA
Posts: 1,314
| | | re: 1.625 should round to 1.63... not 1.62... RIGHT?
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
| | Moderator | | Join Date: Jul 2007 Location: Seattle, WA
Posts: 1,314
| | | re: 1.625 should round to 1.63... not 1.62... RIGHT?
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
| | Familiar Sight | | Join Date: Mar 2008 Location: Southern California
Posts: 221
| | | re: 1.625 should round to 1.63... not 1.62... RIGHT?
Quote:
Originally Posted by Scott Price 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!
| | Familiar Sight | | Join Date: Mar 2008 Location: Southern California
Posts: 221
| | | re: 1.625 should round to 1.63... not 1.62... RIGHT?
Quote:
Originally Posted by Scott Price 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
| | Moderator | | Join Date: Jul 2007 Location: Seattle, WA
Posts: 1,314
| | | re: 1.625 should round to 1.63... not 1.62... RIGHT?
Not a problem, hope you get it working correctly :-)
Regards,
Scott
| | Moderator | | Join Date: Jul 2007 Location: Seattle, WA
Posts: 1,314
| | | re: 1.625 should round to 1.63... not 1.62... RIGHT?
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
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
