The ever elusive calculation/rounding error.

  • Thread starter Thread starter Hal
  • Start date Start date
H

Hal

I have a report with several calculations. I have tried to make all numbers
used in calculations, either Decimal or Currency. The problem is with my
currency values... for example I have the following:

Gallons: 611.94
per gallon: $1.38

Which would give $844.4772 (Access showing: 844.48 in the report)

I then have to use a multiplier (normally 2) which gives $1,688.9544
(Access showing 1,688.95)

and finally 7.5% excise tax ($126.67158) is added (Access showing 126.67)

When I add all these up the actual amount is 1815.62598 (1,688.9544 +
126.67158) in this case the Access report accurately shows it rounded to
1,815.63. When the auditors see the report, they add up the 1,688.95 +
126.67 showing in the report which would give 1,815.62, but with Access
showing the 1,815.63 the report is kicked back. How can I prevent these
discrepancies?

TIA Hal
 
Hal said:
I have a report with several calculations. I have tried to make all numbers
used in calculations, either Decimal or Currency. The problem is with my
currency values... for example I have the following:

Gallons: 611.94
per gallon: $1.38

Which would give $844.4772 (Access showing: 844.48 in the report)

I then have to use a multiplier (normally 2) which gives $1,688.9544
(Access showing 1,688.95)

and finally 7.5% excise tax ($126.67158) is added (Access showing 126.67)

When I add all these up the actual amount is 1815.62598 (1,688.9544 +
126.67158) in this case the Access report accurately shows it rounded to
1,815.63. When the auditors see the report, they add up the 1,688.95 +
126.67 showing in the report which would give 1,815.62, but with Access
showing the 1,815.63 the report is kicked back. How can I prevent these
discrepancies?
Click to expand...


Add up the rounded values (that are displayed). I don't see
where you explained how you're adding up the amounts, but
maybe it should be something like:

=Sum(Round(amount, 2))
 
Back
Top