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?

--
Ken Hudson

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

"Ken Hudson" wrote:

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?

--
Ken Hudson

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



"Mike H" wrote in message
...
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

"Ken Hudson" wrote:

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?

--
Ken Hudson

On Fri, 11 Jul 2008 11:03:01 -0700, Ken Hudson
wrote:

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

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

"Mike H" wrote:

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

"Ken Hudson" wrote:

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?

--
Ken Hudson

Even though you may only be using a couple decimal places, declare the
variables to hold these values as Double and not as Single. On my
Excel2002 if the variable that holds 31.11 is a Single, the product will
be 46.6650009155273 and both Round functions will give you 46.67. Or if
the product is stored in a Single, the product will print out as 46.665
but the VB Round(x,2) will give 46.67. (One problem with using Option
Explicit.)

Mike H wrote:
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

"Ken Hudson" wrote:

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?

--
Ken Hudson