Banker's Rounding in Excel

[DocShock]

I would like to perform Banker's Rounding in Excel. I understand tha
there is no excel function that will do that, but I have read that yo
can do it through Visual Basic. Can something please explain to me ho
this is done - how do I use Visual Basic to get this function to wor
in excel? I am not at that familar with Visual Basic, so please b
gentle.

Thanks very much

 

[Norman Harker]

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 Excel’s 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 Excel’s ROUND and Banker’s 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
Banker’s 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 Excel’s ROUND will bias rounding in an upwards
direction. With Excel’s 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 5’s get
rounded down; half of the 5’s,6,7,8,9 get rounded up.



However, Microsoft’s 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 VBA’s Round function
uses Bankers Rounding.



Microsoft’s 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
(I’m surprised it hasn’t 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
(e-mail address removed)

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/

 

[DocShock]

Thanks for the reply it is much appreciated

 

[Harlan Grove]

...
...

Microsoft’s 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

...

Maybe as you mature you'll develop a healthy skepticism for anything &
everything Microsoft publishes (but I doubt it). Using this wonderful udf, the
worksheet function

=BRound(1.015,100)

returns 1.01 rather than 1.02. Why? Well, simple numeric incompetence on the
part of the individual who wrote this particular KB article would be one
possible answer with contributory negligence in failing to test it thoroughly.
The critical statement is

FixTemp = Fix(Temp + 0.5 * Sgn(X))

which shows that the individual(s) who wrote this udf are remarkably naive about
rounding error. Try this in VBE's Immediate window:

? Fix(101.5 + 0.5) = Fix(1.015 * 100 + 0.5)

Does it return FALSE?

Adding to the fun, debugging the call =BRround(1.025,100) I get the following in
VBE's Immediate window:

? Temp
102.5
? Temp - Int(Temp)
0.49999999999973
? 102.5 - Int(102.5)
0.5
? Temp - 102.5
-2.70006239588838E-13

As **ALWAYS** when working with rounding error, small additive corrections
**MUST** be used. THERE IS NO ALTERNATIVE! In this case, the udf should be
something like


Function BRound2( _
ByVal x As Double, _
Optional ByVal rf As Double = 1 _
) As Double
'--------------------------------------
Dim t As Double, ft As Double, s As Double

s = 1

If x < 0 Then
s = -1
x = -x
End If

t = Fix(x * rf * 10 + 0.1) / 10
ft = Fix(t + 0.5)

If t - Int(t) = 0.5 And ft / 2 <> Int(ft / 2) Then ft = ft - 1

BRound2 = s * ft / rf

End Function


[Pet peeve: this also demonstrates that long variable names are no defense
against algorithmic obtuseness.]

If you don't believe me, you can always test my assertions above. If they hold,
what's your honest assessment of the quality of the VBA code in Microsoft's
ironically named KnowledgeBase?

FWIW, VBA not needed. There are several threads in the archives on performing
bankers rounding using built-in functions only. Here's such a formula that gives
the same results as BRound2.

=SIGN(x)*(ROUND(ABS(x)/rf,0)-(ABS(MOD(ABS(x)/rf,2)-0.5)<rf))*rf

 

[Norman Harker]

Hi Harlan!

I'll advise Microsoft of the problem with KB196652.

Regards
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

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/

 

[Peter T]

Does the backslash divisor not do banker's rounding, to an
integer?

Function bRound3(ByVal x As Double, _
Optional ByVal rf As Double = 1) As Double

bRound3 = (x * rf \ 1) / rf

End Function

I may have misunderstood banker's rounding and/or this
operator. However in limited testing bRound2 & bRound3
appear to give same results, but not bRound1 or indeed the
formula.

Regards,
Peter

 

[Harlan Grove]

Does the backslash divisor not do banker's rounding, to an
integer?

...

From VBE's Immediate window:

? (1.015 * 100) \ 1
101
? (1.025 * 100) \ 1
102
? 1015 \ 10
101
? 1025 \ 10
102

So, no, it doesn't appear to do banker's rounding reliably. Your BRound3 udf
called from the worksheet as =bround3(1.015,100) returns 1.01 instead of 1.02.

There's a considerable amount of (1) literature and (2) collective experience
when it comes to numeric programming. With regard to rounding, *NOTHING* works
*EXCEPT* small biasing steps, i.e., scaling the value to be rounded to an
integer (though still stored as floating point), adding a fractional constant
value to tip almost integers over the integer threshold, e.g., 1.999987654 to
2.000087654, then truncating to integer, then rescaling. The result may not be
precisely represented in floating point, but it should display correctly.

With regard to my BRound2, I was sloppy and used too large a biasing value. I
should have used something on the order of 1E-6 or 1E-6 / rf. The point being
how close a number can be to SomeInteger + 0.5 to be ensured that it's forced to
be slightly greater than that. For example, if 1.015 entered were stored as
1.01499999999999, so multiplying it by 100 just gives 101.499999999999. Adding
1E-14 to it gives 101.500000000000, then add 0.5 to get 102.000000000000, which
truncates to 102. Rescale to 1.02 (which is not exactly represented in binary
floating point). In this particular example, 1E-14 is exactly large enough to
boost 100 times the initial value to an exact sum of powers of 2, but it may not
work for 10.015 or 100.015 etc. Should it be increased to 1E-13 or 1E-12? How
many sequential 9s after a 4 in the critical decimal place are needed before
boosting the scaled value over SomeInteger + 0.5. That's where art enters into
numeric programming.

Attempting to handle rounding error without biasing is destined for the same
success as squaring the circle. But don't let me stop anyone from trying.

 

[Jerry W. Lewis]

=BRound(1.015,100)

returns 1.01 rather than 1.02.

The VBA Round function also returns 1.01 from Round(1.015,2). Only the
worksheet Round function is adequately buffered against the vaguaries of
decimal binary conversion. In VBA
Round(CDbl(CStr(x)), )
helps
http://groups.google.com/groups?selm=3E55A396.8080708@no_e-mail.com
but is not a cure-all as this example shows.

Does anyone know where the term "Bankers rounding" came from? Does
anyone other than Microsoft use that term? The rounding method has been
an ASTM standard for over half a century, but have bankers ever used it?

Jerry

 

[Petter T]

Harlan,

no, it doesn't appear to do banker's rounding reliably

You're right, actually I already (partly) understood the
rounding issue and was surprised that in limited testing
my function appeared reliable, it was late!

Reason - I had generated a series of numbers by formula,
sequentially adding 0.005. I got 100% correct results
applying my function on the sheet, sharply contrasting
with MS's bRound. However paste special my formulas to
values and some errors occurred (but considerably fewer
than with MS's function). This will demonstrate, again
from the Immediate window:

? (1.015 * 100) \ 1
101
? ((1.01 +.005) * 100) \ 1
102

I take this to mean 1.015 <> (1.01 + 0.005)

I tested a bit more with formula numbers and now find my
function is not reliable with formula results between -1
to +1 (using a factor of 100).

For anyone not familiar with the \ operator, the division
of two numbers results in an integer, rounded to the
nearest even number if the division results in a remainder
of 0.5 (banker's rounding). But this assumes 0.5 = 0.5!

? 2.5\1
2
? 3.5\1
4

Regards,
Peter