Round-To-Even (aka banker's rounding)

[Vince]

I am trying to use excel to mimic a VB written program. I
am however running into a problem where the VB progam round
() function rounds-to-even while excel rounds using
standard round .5 up. Does anyone know if it is possible
to have excel round the same way as VB?

[Charles Williams]

Hi Vince,

Stephen Bullen published this in David hager's excel experts newsletter,
available at

http://www.j-walk.com/ss/excel/eee/eee011.txt

POWER FORMULA TECHNIQUE

by Stephen Bullen

This formula perform 'bankers rounding' for a number (Num) to a given number
(Plc) of significant digits.

=MROUND(Num,IF(VALUE(RIGHT(Num/10^(INT(LOG(ABS(Num)))-Plc+1),2))=0.5,2,1)*
SIGN(Num)*10^(INT(LOG(ABS(Num)))-Plc+1))

If you define 'Fact' as =10^(INT(LOG(ABS(Num)))-Plc+1), this reduces to:

=MROUND(Num,IF(VALUE(RIGHT(Num/Fact,2))=0.5,2,1)*SIGN(Num)*Fact

Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

"Vince" <vincem...@hotmail.com> wrote in message
news:102b501c259ab$f4096010$3bef2ecf@TKMSFTNGXA10...

[Alan]

Have you tried ROUNDUP or ROUNDDOWN?

"Vince" <vincem...@hotmail.com> wrote in message
news:102b501c259ab$f4096010$3bef2ecf@TKMSFTNGXA10...

[Jerry W. Lewis]

If you don't object to calling a VBA UDF from Excel, then try

Function RoundEven(num, Optional digits)
If IsMissing(digits) Then digits = 0
RoundEven = Round(CDbl(CStr(num)), digits)
End Function

CDbl(CStr(num)) avoids surprises due to vagaries of binary representations.

Jerry

[Jerry W. Lewis]

As a point of historical interest, does anyone know how this came to be
called "Bankers' rounding"? I have yet to find any evidence that
bankers have ever rounded in this fashion.

Jerry

[Dana DeLouis]

There's a note on Bankers Rounding in this article.

HOWTO: Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q196652

Here's a quick copy...
When you add rounded values together, always rounding .5 in the same
direction results in a bias that grows with the more numbers you add
together. One way to minimize the bias is with banker's rounding.

Banker's rounding rounds .5 up sometimes and down sometimes. The convention
is to round to the nearest even number, so that both 1.5 and 2.5 round to 2,
and 3.5 and 4.5 both round to 4. Banker's rounding is symmetric.

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3D7FC857.4050709@no_e-mail.com...

> As a point of historical interest, does anyone know how this came to be
> called "Bankers' rounding"? I have yet to find any evidence that
> bankers have ever rounded in this fashion.

<snip>

[Jerry W. Lewis]

I am well aware of this method of rounding, it is the standard from
ASTM, IEEE, etc. My question is how it came to be called "Bankers'
Rounding", when (as far as I can tell) bankers have never used it. I
would also be interested any any pre-1940 references of any kind, to
this method of rounding.

Jerry