rounding error

[pkeegs]

My database in Access 2003 has a form which calculates Quantity by the Price
in the footer which is then multiplied by a Tax rate of 12.5% and the two
added to get a Total . I get the following figures in the form Subtotal:
96,738.67 Tax: 12,092.33 TOTAL: 108,831.01. The correct answer should be
108,831.00. I have tried various formats (currency, standard, fixed with
rounding Auto & 2) but am not able to correct for that small difference. Any
solutions

 

[Joe User]

My database in Access 2003 has a form which
calculates Quantity by the Price in the footer
which is then multiplied by a Tax rate of 12.5%
and the two added to get a Total . I get the
following figures in the form Subtotal: 96,738.67
Tax: 12,092.33 TOTAL: 108,831.01. The correct
answer should be 108,831.00.

If you are asking about calculations performed in Excel (this is an Excel
newsgroup, not an Access newsgroup), the simple answer is: all calculations
that should result in dollars-and-cents should be rounded to 2 decimal places.

There are two ways to do that: I prefer explicitly using ROUND in the
formulas. For example,

B1: =ROUND(SUM(A1:A100),2)
B2: =ROUND(B1*12.5%,2)
B3: =ROUND(B1+B2,2)

For example, note that 96738.67*12.5% is actually 12092.33375 if you do the
compuation manually or with a calculator. If you carry the extra fractional
digits through other calculations, eventually you will see noticable "errors"
in expected results.

But in your example, note that 96738.67 + 12092.33375 would still apear to
be 108831.00 when displayed to 2 decimal places. So we can infer that
96738.67 is not exactly that either; in fact, it is between 96738.67125 and
just less than 96738.68125.

There are several ways that might happen; but very likely, it is because
some other intermedicate computation results in more than 2 fractional
digits, just as multiplying by 12.5% did.

PS: The alternative to explicit use of ROUND is to use the option Precision
As Displayed under Tools > Options > Calculation (in Excel 2003). I
deprecate the use of that feature because it can be very dangerous if you are
not careful. If you choose to experiment with PAD, be sure to make a backup
copy of the Excel file before you set the option. PAD will implicitly round
to 2 decimal places only if you use a numeric format (Number, Accounting,
Currency, etc) with 2 decimal places. In particular, it has not impact on
the results in cells formatted as General.

I have tried various formats (currency, standard,
fixed with rounding Auto & 2

Unless you use Precision As Displayed (see above), formats with 2 decimal
places only change the appearance of values, not the actual value in the cell.


----- original message -----

 

[Joe User]

Errata....

So we can infer that 96738.67 is not exactly that either;
in fact, it is between 96738.67125 and just less than 96738.68125.

It is between about 96738.6711111111 and about 96738.6749999999 if the
subtotal plus tax displays as 108,831.01, where subtotal displays as
96,738.67 and tax displayes as 12,092.33. Tax (12.5% of subtotal) is
actually between about 12092.3338888889 and about 12092.3343750000
respectively. Their sum is actually between about 108831.005000000 and
about 108831.009375000.

There are several ways that might happen; but very likely, it is because
some other intermedicate computation results in more than 2 fractional
digits, just as multiplying by 12.5% did.

Another possible source of the "error" in the subtotal (i.e unexpected
fractional digits) is due to the way that Excel (and Access, presumably)
store numbers and perform arithmetic. It is caused binary floating point.
Consequently, most numbers with fractional digits cannot be represented
exactly. Summing a large quantity of such numbers might introduce a
significant "error".

(I was trying to avoid the topic.)


----- original message -----