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

[n8kindt]

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

[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

[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

[n8kindt]

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!

[n8kindt]

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

[Scott Price]

Not a problem, hope you get it working correctly :-)

Regards,
Scott

[Scott Price]

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

[homeflsh]

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