## Excel VBA Tip: Banker’s Round vs. Round

Monday, June 2nd, 2014 @ 8:19 pm | Excel VBA

When using the Round() function on a worksheet or referencing this via Application.WorksheetFunction.Round() in code, things work as one would expect:  For example, Round(1.5,0) rounds 1.5 up to 2 and returns 0 decimal places, so the calculation Round(1.5,0) returns 2.  Similarly, Round(2.5,0) returns 3.  All as expected.  But, if you try using Round in VBA, watch out for an odd anomaly known as the Banker’s Round.  Apparently created with the goal of minimizing bias (when you accumulate sums of rounded up numbers, you get an upward bias.  Instead, the Banker’s Round is designed to round to the nearest EVEN integer.  This results in round(1.5,0) = 2, all good, but round(2.5,0) also equals 2.

I came across this today when trying to match primary keys where a one to many relationship exists, so had no other choice but to match on an additional numeric value as a secondary key.  Yes, this is insane.  So I’ll be replacing Round() in my code with Application.WorksheetFunction.Round() and will also return a match if the absolute value of the difference of the rounded numeric values is less than or equal to 1.

## Recently

• Excel Workbook for Options Pricing and Greeks
• Risk-Neutral Pricing of Options post-Black Scholes
• GameStonk! The wild rollercoaster of GameStop’s stock
• Black Scholes Option Pricing with Excel VBA
• Use Python to Get Stock Information
• Installing Quantlib and Boost on your PC
• Excel VBA Tip: Banker’s Round vs. Round
• Agency ARM MBS Sector
• Prepayments on Hybrid ARM MBS
•