Hi DocShock!
Here's a resource I put together from previous questions on Banker's
Rounding:
The Bankers Rounding algorithm may be stated as:
Where the residual to be discarded is equal to 5, we round to the
nearest even digit. This contrasts with Excels ROUND algorithm which
in such cases rounds up to the next digit.
Consider the following where we have three decimal places of data and
wish to round to two decimal places:
Examples of no difference between Excels ROUND and Bankers Rule:
12.374 discarded residual <> 5 both round to nearest second digit
12.37
12.376 discarded residual <> 5 both round to nearest second digit
12.38
12.375 discarded residual = 5 Excel ROUND rounds up to 12.38
Bankers Rounding also rounds up to 12.38 because it is the nearest
digit to be rounded to that is an even number.
Example of a difference:
12.385 discarded residual = 5. Excel ROUND rounds up to 12.39
Bankers Rounding rounds down to 12.38 because it is the nearest digit
to be rounded to that is an even number.
It should be clear that Excels ROUND will bias rounding in an upwards
direction. With Excels ROUND residual of 1,2,3 and 4 get rounded down
but residuals of 5,6,7,8,9 get rounded up with residual of 0
unchanged. With Bankers Rounding 1,2,3,4 and half of the 5s get
rounded down; half of the 5s,6,7,8,9 get rounded up.
However, Microsofts KB Article 196652 indicates that even Bankers
Rule rounding will show a bias.
User Defined Function for Bankers Rounding
Function BANKROUND(Number As Double, Digits As Integer) As Double
BANKROUND = Round(Number, Digits)
End Function
This User Defined Function uses the fact that VBAs Round function
uses Bankers Rounding.
Microsofts KB Article 196652 also provides a User Defined Function
that uses a Factor instead of Digits for rounding:
Function BRound(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
' For smaller numbers:
' BRound = CLng(X * Factor) / Factor
Dim Temp As Double, FixTemp As Double
Temp = X * Factor
FixTemp = Fix(Temp + 0.5 * Sgn(X))
' Handle rounding of .5 in a special manner
If Temp - Int(Temp) = 0.5 Then
If FixTemp / 2 <> Int(FixTemp / 2) Then ' Is Temp odd
' Reduce Magnitude by 1 to make even
FixTemp = FixTemp - Sgn(X)
End If
End If
BRound = FixTemp / Factor
End Function
Microsoft indicates that Bankers Rounding will still produce some bias
but that using random approaches to the problem could result in
providing two different totals for the same set of data.
Official Sources on Bankers Rounding
ISO 31-0 :1992, Quantities and units - Part 0 : General principles,
Annex B (Informative) (Guide to the rounding of numbers) Priced CHF 95
(Im surprised it hasnt been rounded to CHF 100 <vbg>)
http://www.iso.ch/iso/en/ISOOnline.frontpage
SI10-2002 IEEE/ASTM Standard for Use of the International System of
Units (SI): The Modern Metric System 2002 SI10-2002 IEEE/ASTM Standard
for Use of the International System of Units (SI): The Modern Metric
System 2002. Priced US$50
http://shop.ieee.org/store/default.asp?tabtype=stand
Microsoft have a Knowledge Base article in Custom Rounding:
HOWTO: Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;EN-US;196652
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
***@optusnet.com.au
It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
Post by DocShock >I would like to perform Banker's Rounding in Excel. I understand
that
there is no excel function that will do that, but I have read that
you
can do it through Visual Basic. Can something please explain to me
how
this is done - how do I use Visual Basic to get this function to
work
in excel? I am not at that familar with Visual Basic, so please be
gentle.
Thanks very much.
---
Message posted from http://www.ExcelForum.com/