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...
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
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