.Net Rounding - ToEven v.s AwayFromZero

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.