The Old Rounding Question

[Ken Hudson]

Just looking for some reassurance in my thought process.
I want to multiply 31.11 by 1.5 in VB and I want VB to round the answer to
46.67.
Using the Round function will give me 46.66.
Using Application.Round is the method I need to get 46.67, correct?

 

[Mike H]

Hi,

I'm not sure what the reassurance is you are looking for. Worksheet
functions don't use 'Bankers' rounding so the result for .5 is always up. The
VB round function uses 'bankers' rounding hence n.665 rounds to n.66.

I'm sure there's a more involved explanation somewhere but in the meantime
HTH.

Mike

 

[Rick Rothstein \(MVP - VB\)]

There is one function in VB that uses "normal" rounding... the Format
function.

Value = 31.11 * 1.5
MsgBox Format(Value, "#.##")

The only problem is that if your value is a whole number, the output from
Format will include a trailing decimal point. If you don't mind trailing
zeroes, then use this....

MsgBox Format(Value, "0.00")

Otherwise, you will need something like this...

If Value = Int(Value) Then
MsgBox Format(Value, "0")
Else
MsgBox Format(Value, "0.##")
End If

Personally, I would prefer this one-liner solution myself....

MsgBox Format(Value, "0" & Mid(".##", 1 - 3 * (Value = Int(Value))))

Rick

 

[Ron Rosenfeld]

Just looking for some reassurance in my thought process.
I want to multiply 31.11 by 1.5 in VB and I want VB to round the answer to
46.67.
Using the Round function will give me 46.66.
Using Application.Round is the method I need to get 46.67, correct?

You are correct. The VBA Round function uses a different convention (sometimes
called bankers rounding, for reasons no one seems to know) than does the ROUND
Worksheet function.
--ron

 

[Mike H]

Hi,

Found this

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

As you will note Microsoft applications round inconsistently and the reason
is ....Historical... Well that clears that up

Mike