software

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

Paul
On Thu, 2 Feb 2006 06:56:55 -0800, gt; wrote:

gt;Does anyone know if the format function (quot;Format/Number with x number of
gt;decimal places) happens to conform with ASTM E29 for the last right-hand
gt;digit...???
gt;
gt;
gt;Thanks
gt;
gt;Paul
gt;

Format function? Are you talking about the VBA Format function?

If so, it does not, as I understand you. The Format function returns a string
of digits rounded according to the format expression. The rounding is done in
the same method as the Format Cells dialog in Excel.

Of course, the Format function actually changes the value. Whereas the Format
Cells dialog only changes how the value is displayed.

The VBA Round Function in VBA6 does conform to the standard, as far as I know.
It rounds the midway numbers to the nearest even number.--ron

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 support.microsoft.com/default...;EN-GB;q194983 and
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

gt; wrote in message
...
gt; Does anyone know if the format function (quot;Format/Number with x number of
gt; decimal places) happens to conform with ASTM E29 for the last right-hand
gt; digit...???
gt;
gt;
gt; Thanks
gt;
gt; Paul
gt;
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
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 quot;Banker's
Roundingquot;?

Jerry

quot;Bernard Liengmequot; wrote:

gt; Hi Paul,
gt; A simple experiment will show you that 1.575 rounds to 1.58 while 1.585
gt; round to 1.59 with formatting and with the ROUND function. Clearly Excel
gt; does not use the ASTM E29 protocol (aka Banker's Rounding)
gt;
gt; However, VBA does follow ASTM E29
gt; (see support.microsoft.com/default...;EN-GB;q194983 and
gt; support.microsoft.com/default...b;EN-US;196652)
gt; So the user-defined function below will round both 1.575 and 1.585 to 1.58
gt; Function myround(rng, fig) myround = Round(rng, fig)End Functionbest wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; gt; wrote in message
gt; ...
gt; gt; Does anyone know if the format function (quot;Format/Number with x number of
gt; gt; decimal places) happens to conform with ASTM E29 for the last right-hand
gt; gt; digit...???
gt; gt;
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Paul
gt; gt;
gt;
gt;
gt;

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

quot;Jerry W. Lewisquot; gt; wrote in message
...
gt; VBA's Round function does not handle the vaguaries of binary
gt; approximations
gt; very well, for instance myround(1110*0.0865,2) will round down instead of
gt; up.
gt; Your code will often produce more satisfactory results if you use
gt; Round(CDbl(CStr(rng)), fig)
gt;
gt; Also, VBA's Round function does not support negative arguments in the way
gt; that the worksheet function does. The code I posted at
gt; groups.google.com/group/micro...7fce6145b70d69
gt; deals with this shortcoming.
gt;
gt; Does anyone know of any instances where bankers have EVER rounded in this
gt; way? Barring that, does anyone know how this came to be called quot;Banker's
gt; Roundingquot;?
gt;
gt; Jerry
gt;
gt; quot;Bernard Liengmequot; wrote:
gt;
gt;gt; Hi Paul,
gt;gt; A simple experiment will show you that 1.575 rounds to 1.58 while 1.585
gt;gt; round to 1.59 with formatting and with the ROUND function. Clearly Excel
gt;gt; does not use the ASTM E29 protocol (aka Banker's Rounding)
gt;gt;
gt;gt; However, VBA does follow ASTM E29
gt;gt; (see support.microsoft.com/default...;EN-GB;q194983 and
gt;gt; support.microsoft.com/default...b;EN-US;196652)
gt;gt; So the user-defined function below will round both 1.575 and 1.585 to
gt;gt; 1.58
gt;gt; Function myround(rng, fig) myround = Round(rng, fig)End Functionbest
gt;gt; wishes
gt;gt; --
gt;gt; Bernard V Liengme
gt;gt; www.stfx.ca/people/bliengme
gt;gt; remove caps from email
gt;gt;
gt;gt; gt; wrote in message
gt;gt; ...
gt;gt; gt; Does anyone know if the format function (quot;Format/Number with x number
gt;gt; gt; of
gt;gt; gt; decimal places) happens to conform with ASTM E29 for the last
gt;gt; gt; right-hand
gt;gt; gt; digit...???
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt; gt;
gt;gt; gt; Paul
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;