Database Tips: Rounding Errors

We rely on our databases for accurate calculations. We like to see information generated "on the fly" from raw data. But a bias among managers, going back to the early days of spreadsheets can lead us to thwart this process and introduce inaccuracy.

For this tip, we're going to look at spreadsheets. You can apply the lesson to your particular database program, and you may need to apply it to the spreadsheets that feed those database programs.

VisiCalc was the product that launched the era of the electronic spreadsheet. It dominated the market, and was popular with engineers until Lotus Corporation came out with a more robust product.

When Lotus released its 3-in-1 product, Lotus 1-2-3, the use of the electronic spreadsheet made a quantum leap forward. This product was a:

1. Spreadsheet. You could perform calculations, analysis, and manipulation of data.

2. Database. You could store data in a flat table, and even perform queries.

3. Graphics package. You could represent data with charts and graphs.

Borland proved to be serious competition to Lotus, but Microsoft ultimately trumped them both. In 1994, Microsoft released a version of Office that boosted Excel into the dominant position as the spreadsheet/workbook tool of choice. Today, Excel still reigns.

And it's often used to create the data that you find in your databases--sales projections, for example. For many people, Excel serves all of their database needs. This is much to the ire of IT admins, who must then try to resolve the "islands of information" problem. But even if you resolve that problem, Excel issues can still create data accuracy problems.

Though Excel is in wide use (or rampant use, depending on your viewpoint), many users don't understand the basics of the program. When it comes to rounding, that is certainly the case.

By default, Excel rounds a number for display purposes but uses the unrounded number for calculation. This means you may "see" an "error" in the math--but what you are really seeing is display vs. actual. A way to fix this is to increase the number of digits displayed. But the display is really for reference. What counts is the calculation.

1. Excel does the calculation with actual numbers.
2. If you format the numbers as currency, Excel displays the product of each row as rounded. But, it maintains the actual (unrounded) numbers behind the scenes for purposes of calculation.
3. The total for each worksheet is rounded for purposes of display by the "format as currency" command.

Let's say you keep this in mind, and thus don't use the ROUND command except for the final number. (If you use currency formatting, you don't need to use ROUND at all). That is, you don't use ROUND during calculation. This will result in a maximum error of less than 0.49999 cents (we can assume 5 decimal places is adequate, for purposes of illustration here).

So, whether you have 10 rows or 10,000 rows, your maximum error will be statistically insignificant.

But, that's not true if you are rounding during the calculation process. The maximum error, if using the ROUND function for each row would be (0.49999 * N), where N is the number of rows. So for 20 rows, the maximum error is then $10.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.