435,149 Members | 885 Online
Need help? Post your question and get tips & solutions from a community of 435,149 IT Pros & Developers. It's quick & easy.

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

 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
7 Replies

 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

 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

 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

 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

 Expert 100+ P: 1,384 Not a problem, hope you get it working correctly :-) Regards, Scott Mar 14 '08 #6

 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