Rounding up half penny

[BeallDon]

I'm using the "round" function in my form to do tax calculations but when the result is 3 decimal places and ending in a 5, the "round" function rounds it DOWN. Is the something I could do to make it round UP if the result is half a cent?

Gross = me.GrossAmount (from the form)
CommissionRate = 15%
CommissionAmount = [Gross] * [CommissionRate]
TaxRate = 5%
TaxAmount = [CommissionAmount] * [TaxRate]

example 1:
Gross = 195
CommissionAmount = 29.25
TaxAmount = 1.4625 ...... Round([TaxAmount],2) results in 1.46 which is correct....however...

example 2:
Gross = 190
CommissionAmount = 28.50
TaxAmount = 1.425 ...... Round([TaxAmount],2) results in 1.42 instead of the desired result of 1.43.

Any suggestions would be appreciated.

 

[MajP]

See other thread
VBA uses bankers rounding, therefore you get these results. This discussion explains the many ways to round and provides custom functions.

http://support.microsoft.com/kb/196652

 

[PHV]

To avoid the bankers rounding you may use something like this:
CDec(Format(TaxAmount,"0.00"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

If you use vba in excel, you can use WorksheetFunction.Round, that works as you need and in the same way as ROUND function in excel.

combo

 

[MajP]

It is pretty bizarre and something I never understood. As pointed out vba round does bankers rounding, the format function does arithmetic rounding, the excel worksheet function does arithmetic rounding. The convert functions like CINT will do bankers rounding. WTF?? MS, pick one and stick with it!

 

[strongm]

To be fair VBA was developed separately to Excel and was based off VB - which, as you are aware, uses bankers rounding. And so all its rounding functions (including CINT) can be considered to be unrelated to Excel's functions.