Home > Custom Development, Refactoring, Software Development, TDD > My two and a half cents – pitfalls with rounding

My two and a half cents – pitfalls with rounding

Secret Server at FOSE tradeshow

March 19th 2009 | Tucker Croft

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Calibri”,”sans-serif”;}
True or False: these values are the same?

1.       61.64598913 saved to DataBase Column :[Age] [decimal](10, 2)

2.        Math.Round(61.64598913, 2)

What does (1. == 2.) return?

False. Bryant and I came across an interesting issue when comparing two values that appeared identical, but which the code insisted were different.   Our research lead us into the internals of ASP.Net rounding to solve an infrequent but legitimate concern.

Scenario

The above question became my issue. I needed to know why a portion of the system would compare a value in the database to the same newly-calculated value and judge a significant difference.

The scenario, using Age as the example:

  • CalculateAge() – A value is calculated with up to 15 decimal places
  • AgeInDB – The value is stored in a database table with precision 2
    • [Age] [decimal](10, 2)
  • AgeChangeDetector – Determines if the Age has changed by comparing CalculateAge() to AgeInDB
  • If Age had changed flags the value for review
  • If Age hadn’t change then doesn’t need to be reviewed

After several months and thousands of values being compared correctly,  I found a single value being repeatedly marked for review even though the raw Age was not changing.

  • Calculated Age(): 61.64598913
  • AgeInDB: 61.65

At first glance, this seemed to be a simple failure to compare the values at the same precision. Since the database is at precision 2 and the new calculated value is up to 15 decimal places, the same value would appear different if compared directly. But looking at the comparison the code was:

  • Math.Round(caculatedAge, 2) == AgeInDatabase

With the Math.Round function both values were being compared to the same decimal places. I checked the AgeChangeDetector tests and all test cases passed with a variety of different decimal number combinations.  Curious, I plugged the mysterious values into the AgeChangeDetector class and saw my assertion fail; the class detected a difference with the AgeInDB at 64.68 and the rounded calculation at 64.67.  Seeing the 64.67, I had isolated the problem to the Math.Round function.

Banker’s Rounding

Jumping on Google, I searched for “problems with Math.Round” and filtered down to a forum about the internal algorithm.  Rounding is, at its core, a lossy method where a number right in the middle (.5) must be given a bias and go up or down to 1 or 0.  From grade school, everyone is familiar with common rounding which always rounds .5 up to 1.  When saving the decimal value to precision 2, the database used this method, hence the 61.65.  But Math.Round uses the algorithm known as Banker’s Rounding.  Banker’s Rounding strives to equal things out by rounding up as often as it rounds down.  In a transaction, one party would gain the half a penny and one party would lose it.  To achieve an unbiased view of the middle number, bankers round to the closest even number.  Since having an even number before the .5 is as statistically likely as having an odd, the bias evens out over multiple transactions.  My problem boils down to the fact that ASP 1.1 Math.Round will always use Banker’s Rounding and my database is using common method.

Solution

Knowing the problem may be half the battle, I now had some potential solutions that didn’t seem easy.  One option was that I could round every number in code first before saving to the database. This would ensure that all values going (and coming) from the database would be uniform with my calculations. But that would be a big impact type of change and add an extra level of processing on every decimal value persist to the database (and this might need to expand to more than just the AgeInDB column).  Also, I had to keep in mind that the database had already saved all its numbers and there would be no way to know if the number had been rounded up because it landed on .5 or because it had landed above that.  Another option would be  that the comparison was all that really needed to change, so I researched a means to accomplish common rounding in the AgeChangeDetector.

If I was  in ASP 2.0, Math.Round would take an optional parameter that can set the rounding algorithm, but I am tied to ASP 1.1 in the current release.  After receiving input from forums, I created a new Method to round numbers using the built-in rounding in the ToString method.  My function looked like this:

public decimal CustomMathRound(decimal number, int places)

{

string decimalPad = “#.” + string.Empty.PadRight(places, ‘#’);

string nums = number.ToString(decimalPad);

return Convert.ToDecimal(nums);

}

I replaced the call to Math.Round with my custom function and the new test passed without disturbing any other functionality.  Feeling confident that this was a workable fix, I replaced all other comparisons between a database value and a calculated value with my custom function to avoid another change conflict in the future.

Follow-up

Rounding is a common task for a developer, but should be considered thoroughly to ensure that different methods are not in play on each side of the comparison. The thousands of correct values before this issue proves comparing numbers to a given significance can catch you off guard. This problem is such an edge case that even in-depth test cases may not expose it. This is a situation that the developer must be aware of beforehand-to specifically test a number impacted by banker’s rounding.


Tucker Croft is a Senior .NET developer at Thycotic Software Ltd. Thycotic is recognized for providing Agile TDD Training and Agile .NET Consulting Services, and its flagship password management software Secret Server.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: