Real rounding vs fake rounding

Sunday 8 September 2019 @ 8:00 pm

Crystal makes it easy to reduce the number of decimals that you display for numeric values. There are two buttons on the toolbar (near the percent sign) and these allow you to either reduce or increase the decimals displayed. When you reduce decimals the remaining digits will either round up or down based on the digits no longer displayed. In other words, if you are showing 75.28 and you click the button to reduce the decimals displayed by one, the value will display as 75.3. This is because the hidden 8 causes the value to round up to the next 1/10th.

However, this is deceptive. The underlying value in the report is still 75.28. And if you were to total that column or use that value in a calculation the value used would still be 75.28. This can give unexpected results, because the visible total at the end of the report might not reflect the sum of the visible values that go into that total. For that reason, I call this ‘fake’ rounding. You will find the same type of issue in Excel spreadsheets.

Most of the time, this isn’t an issue. And many people who read financial statements understand ‘rounding errors‘. But there are cases where you need ‘real’ rounding. Real Rounding requires writing a formula that uses the Round() function. For instance, if you have to apply a tax rate to a purchase and the calculated tax amount.  You could use a formula like this:

{Sales.Charge} * {Sales.TaxRate}

But if the tax value has more than two decimals, those extra decimals aren’t real.  Most transactions have to be rounded to the nearest penny. In these cases you would need a formula like this:

Round ( {Sales.Charge} * {Sales.TaxRate} , 2 )

This rounds the tax to the nearest penny.  If you used the first formula and created a total of the tax values, the total would include all the fictional fractions of a penny.  If everything was displayed with two decimals the total tax would not match the individual tac values, because those would be using fake rounding to display only two decimals.  But if you use the second formula and then create a total of that formula the total should reflect the visible values.  The total calculation is using values that display the same number of digits as the underlying value.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Recrystallize Pro