Since the early dates of Office and VBA, there has been some subtle difference/discrepancy between how rounding is handled within the same platform. (Yes, I count Office and VBA as one platform). If this is new to you, try this:
In Excel,
Or in Word
Insert -> Field -> = (Formula)
type the following in the shaded area
then hit Shift + F9, you get this:
However, if you try to round the same number in VBA as below, you get a different result 2.22
To confuse the matter even more, if you call VBA's Format() method, you get 2.23.
Most people only are accustomed to one way of rounding - less than 5 down and equal or more than 5 up. But sometimes, this method creates bias in calculations. I don't think I can explain better than Wikipedia on this topic, so please refer to http://en.wikipedia.org/wiki/Rounding
Clearly, VBA's Math.Round() method is applying the Round To Even way (alternatively called Banker's rounding). Number 22.225 becomes 22.22 for 2 decimal place rounding, because the 3rd digit is 5 and the digit to the right of it is an even number.
Unfortunately, VBA Math.Round() doesn't give you a way to specify different way of rounding. Moving to .Net, Math.Round() has a overloaded version that takes a MidpointRounding enum to allow you to specify either round To Even or Away From Zero (which is the less-than-5-down- and-equal-or-more-than-5-up method).
Math.Round(22.225, 2, MidpointRounding.ToEven) = 22.22
Math.Round(22.225, 2, MidpointRounding.AwayFromZero) = 22.23
If you don't specify the MidpointRounding enum, then the rounding is defaulted to ToEven:
Math.Round(22.225, 2) = 22.22
If you are unaware of this subtlety when programming number intensive application, or application involves Office application, you will run into hard-to-detect bugs sooner or later.
.Net provides some convenience methods (for instance Format() in VB.Net and ToString() in C#) for you to format a number for presentation:
22.225.ToString("##.##") returns a string "22.23"
but the rounding used is Away From Zero - again different from the default Math.Round().
If you are programming with .Net, it is likely that you also use MS SQL Server, which rounds away from zero as well.
I summarise the rounding difference in the following table:
So if you programme using .Net against Excel/Word or SQL Server, make sure you know when to use:
Math.Round(number, decimal_places, MidpointRounding.AwayFromZero)
And if you use .Net formatting methods, make sure the number appears the same anywhere else across your application.
