When comparing values from various systems, I’d sometimes notice $0.01 differences.
I put the differences down to rounding, but it seemed that no matter which approach I took, the differences could not be reconciled.
It’s one of those things that really bugged me, and I’d feel guilty if I spent too much time on the problem… after all, the differences added up to a lot less than the cost of my time.
The other day when I was comparing values in Excel I used the =ROUND() function and was shocked to see my old friend, the $0.01 difference.
The originating system was one designed by me using VBA, so I took the opportunity to look a bit deeper.
After some time, I isolated the problem to the VBA Round function.
In the image below, A1 to A20 are digits 0.0 to 1.9, B1 downward contains the formula =ROUND(A1, 0), and C1 down contains the formula =VBA_Round(A1, 0).
VBA_Round is a user defined function:
VBA_Round = Round(number, num_digits)
Excel will round 0.5 to 1, and VBA will round 0.5 to 0, yet they’ll both round 1.5 to 2.
It turned out that Excel and VBA use a different method of rounding, and it is by design.
Excel uses arithmetic rounding, and VBA uses Banker’s rounding. Bankers rounding means that you round a 5 to the nearest even number. There’s even a Microsoft Knowledge Base article about it.
For further reading, EWBI also has very good information on the topic.