Re: Rounding Error Driving Me NUTS!

Tech-Archive recommends: Fix windows errors by optimizing your registry

Dirk, thanks for your thoughtful response. I am familiar with the round the
sum versus sum the round issue. My formula is meant to muliply, round, then
add, thus giving me a consistent result. My issue is that Access seems not to
be following my logic (Imagine That....) I meant to muliply, round, add, then
sum in that order.

The only difference between the reports that are consistent and the one that
isn't is that the inconsistent report does not have any detail display.
Perhaps if I include a detail section, then simply don't print it, that would
work? What's the strategy for doing that?
--
Jim


"Dirk Goldgar" wrote:

"JimS" <JimS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:385D9AE0-C838-4C1C-BD40-CB9803AF1BB9@xxxxxxxxxxxxxxxx
I have several reports and an excel extract, all from the same basic
time*** table. The reports take the hours times the rates for each of
three
time categories (standard, OT, DT.) There are thousands of transaction.
The
hours have unlimited decimals, both hours and rates can be null. Misc
Dollars
(reimbusements) are unlimited decimals, typically zero.

I've set up a formula for the total dollars as follows:

=Sum(Round(nz([stdhours])*nz([strate]),2)+Round(nz([othours])*nz([otrate]),2)+Round(nz([dthours])*nz([dtrate]),2)+Round(nz([miscdollaramt]),2))

This works for the reports and excel export, but for the summary report
(which summarizes over job title), it fails. It has a several-cent
rounding
error. I use exactly the same formula throughout all sum levels. In the
summary report, there is no detail level.

It's driving me nuts. Of course, it's only 2 cents on $361,000 but drives
the accountants nuts, who are happy to share.


What leads you to say there's a rounding error? That's not a flip
question -- the way rounding works isn't always the way people expect it to
work. There are several factors that could be contributing to the problem:

1. Data types. Are you storing your dollars and rates in Currency fields --
not just formatted as currency, but actual Currency field types? If not,
you'll be introducing more imprecision into your results than you need to
be. I'm not sure the Currency type is appropriate for your rate fields
(they may need more than 4 decimal places), but most likely it is; and your
miscdollaramt field almost certainly ought to be Currency.

2. Banker's Rounding. Access uses "banker's rounding", which is a way of
evening out rounding error. It's not as simple as rounding digits 1-4 down
and digits 5-9 up, which is what we all learned in grade school. If you're
not familiar with this, google for "banker's rounding" to get a better
explanation than I can give.

3. The difference between "adding rounded numbers" and "rounding added
numbers". I suspect this is the main contributor to problem. If you do
your rounding at the detail level, and then simply sum the rounded values to
create totals, your totals will always be the same as you would get by
adding up all the detail lines. But if you sum the unrounded detail values,
and then round the sum, you're probably going to get a different result.
That's because rounding the sum onlyintroduces rounding error at the very
end of the process. If you round the detail values first, a small rounding
error is introduced for each detail record, and then those small errors are
all added up into the total, which may therefore have a much greater
cumulative rounding error.

In your particular case, it looks to me like your rounding should be applied
at the detail level, since presumably you're calculating dollars that are
actually going to be paid out at that level. Therefore, you should base all
your reports on a query that does this multiplying and rounding calculations
for each detail record. By basing your reports on that query, all you need
to do for your group totals is sum the values, not round them.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

.

Quantcast