Excel VBA Tip: Banker’s Round vs. Round
Jun 02, 2014 in 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.