In troubleshooting a rounding discrepancy a couple of years ago in a software component, I discovered what may be a lesser known fact – the Microsoft programming languages (VBScript, VBA, VB6, VB.NET, C#, etc.) implement what is commonly referred to as “banker’s rounding” or “rounding to the nearest” – which follows the IEEE Standard 754, section 4. My reason for posting about this is that, in some cases, this can yield unexpected results.
As we all know traditional arithmetic rounding (the kind we all learned in grade school) says .5 always rounds up. However, the IEEE Standard 754, section 4 does not implement this form of symmetrical arithmetic rounding.
Here’s the explanation:
Given the range 0 – 1
[.1 .2 .3 .4] – always rounds down
[.6 .7 .8 .9] – always rounds up
However, .5 is the midpoint between 0 and 1 and always rounding .5 in the same direction results in a bias that grows with the more numbers you add together.
So, the IEEE Standard says that one way to minimize the bias is with “banker’s rounding” which is as follows:
Banker’s rounding rounds .5 up sometimes…and down sometimes. I know it sounds inconsistent, but the convention is to round to the nearest even number, so that both 1.5 and 2.5 round to 2, and 3.5 and 4.5 both round to 4. Banker’s rounding is symmetric.
Here are a few sample results using the IEEE Standard (banker’s rounding):
Rounded to 2 Decimal Places
|0.1250||0.12 (Rounds down when the number to the left of the 5 is even)|
|0.1350||0.14 (Rounds up when the number to the left of the 5 is odd)|
|0.1251||0.13 (Rounds up because the 1 to the right of the 5 since it is not exactly the midpoint between .12 and .13)|
|0.125000001||0.13 (Rounds up for the same reason as the one above)|
You may already be aware of this seemingly odd behavior, but many of you may be surprised by this result (as I was).
In any case, I wanted to share my findings in case you were expecting a rounding implementation different than that which is actually implemented in the products/programming languages you are using.
Microsoft acknowledges that their rounding implementation is inconsistent across products (VBScript, VBA, VB6, .NET, SQL Server, EXCEL) and says that this is only done for historical reasons. Here is the link to the Microsoft KB article that details the different rounding implementations in their products and how to implement a custom rounding method based on your need: http://support.microsoft.com/?kbid=196652
Here’s the breakdown of the rounding implementations for Microsoft Products:
|VBScript, VBA, VB 6.0||Banker’s Rounding|
|Excel Worksheet||Symmetric Arithmetic Rounding|
|SQL Server||Either Symmetric Arithmetic Rounding or Symmetric Round Down (Fix) depending on arguments|
|.NET 1.0, 1.1||Banker’s Rounding|
|.NET 2.0 (or greater)||Banker’s Rounding but provides additional overloads to the Math.Round() method for specifying desired midpoint rounding behavior.|
So, the good news is that if you are using SQL Server or Excel rounding, you are fine – you’re gonna get good ‘ol traditional arithmetic rounding (.5 always rounds up).
The key difference between this and Symmetric Arithmetic Rounding (the kind we learned in grade school) is as follows:
Symmetric Arithmetic Rounding always rounds up, where “up” is defined as “away from zero.”
So, 0.1250 rounds to 0.13 and -0.1250 rounds to -0.13.
Asymmetric Arithmetic Rounding always rounds up, where “up” is defined as “heading towards positive infinity.”
So, 0.1250 rounds to 0.13 and -0.1250 rounds to -0.12 (i.e. in a positive direction).
As for my case, I wanted Symmetric Arithmetic Rounding and followed the instructions in the KB article above for implementing my own custom Round method to achieve this result.
Here is the final implementation:
/// Implements alternate rounding methods, in contrast to Decimal.Round()
/// and Math.Round(), which implement banker's rounding (5 rounds to even).
/// See http://support.microsoft.com/?kbid=196652 for other methods.
public static class MathExt
/// Rounds using arithmetic (5 rounds up) symmetrical (up is away from zero) rounding
/// <param name="d">d: A System.Decimal number to be rounded.</param>
/// <param name="decimals">The number of significant fractional digits (precision) in the return value.</param>
/// <returns>The number nearest d with precision equal to decimals. If d is halfway between two numbers, then the nearest whole number away from zero is returned.</returns>
public static decimal Round(decimal d, int decimals)
decimal factor = Convert.ToDecimal(Math.Pow(10, decimals));
int sign = Math.Sign(d);
return Decimal.Truncate(d * factor + 0.5m * sign) / factor;
I would encourage you to review any rounding code in your apps and verify that the rounding implementation is the one you expected.