Rounding Error Driving Me NUTS!

[JimS]

I have several reports and an excel extract, all from the same basic
timesheet 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.

Help!

 

[Dirk Goldgar]

I have several reports and an excel extract, all from the same basic
timesheet 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.

 

[JimS]

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

I have several reports and an excel extract, all from the same basic
timesheet 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)

 

[Dirk Goldgar]

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?

I suppose you could add a detail section but set the Visible property of the
detail section to False. Access does create its own, internal queries for
reports, so if its tinkering is causing the inconsistency, maybe having an
unprinted detail section will keep it from doing that. Please write back
and let us know if it fixes the problem.

 

[Red17]

I have several reports and an excel extract, all from the same basic
timesheet 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.

Help!

I have come accross this issue before. It appears that Access has a
rounding issue with calculations that goes back to Access 2.

To overcome the problem I wrote the following function and replace
Round with RoundFix when carrying out that calc.

Function RoundFix(ctlFieldName, intDecimals) As Double
'Fixes rounding errors for values approaching a round off amount (0.5)
like
'0.499999999999999999999 where the value is in effect 0.5 and should
'round up not down. This usually occurs as the result of a floating
'point decimal problem during a calculation

'Error handling
On Error GoTo RoundFix_Err

Dim dblRoundNum As Double

'Add 0.00000000001 to the number to ensure that the value of the
returned amount
'is greater than 0.5
dblRoundNum = ctlFieldName
dblRoundNum = dblRoundNum + 0.00000000001

'Round up to the designated number of decimal places
dblRoundNum = Round(dblRoundNum, intDecimals)
'Return the function's value
RoundFix = dblRoundNum

RoundFix_Exit:
Exit Function

RoundFix_Err:
MsgBox Err.Description
Resume RoundFix_Exit

End Function

Hope it helps