Many thanks, Jerry
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jerry W. Lewis" wrote in message
...
VBA's Round function does not handle the vaguaries of binary
approximations
very well, for instance myround(1110*0.0865,2) will round down instead of
up.
Your code will often produce more satisfactory results if you use
Round(CDbl(CStr(rng)), fig)

Also, VBA's Round function does not support negative arguments in the way
that the worksheet function does. The code I posted at
http://groups.google.com/group/micro...7fce6145b70d69
deals with this shortcoming.

Does anyone know of any instances where bankers have EVER rounded in this
way? Barring that, does anyone know how this came to be called "Banker's
Rounding"?

Jerry

"Bernard Liengme" wrote:

Hi Paul,
A simple experiment will show you that 1.575 rounds to 1.58 while 1.585
round to 1.59 with formatting and with the ROUND function. Clearly Excel
does not use the ASTM E29 protocol (aka Banker's Rounding)

However, VBA does follow ASTM E29
(see http://support.microsoft.com/default...;EN-GB;q194983 and
http://support.microsoft.com/default...b;EN-US;196652)
So the user-defined function below will round both 1.575 and 1.585 to
1.58
Function myround(rng, fig) myround = Round(rng, fig)End Functionbest
wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
...
Does anyone know if the format function ("Format/Number with x number
of
decimal places) happens to conform with ASTM E29 for the last
right-hand
digit...???


Thanks

Paul