Currency rounding problem

[Guest]

Have almost exactly same problem as another recent post with rounding
problems but don’t know which formula to solve it with. Although two totals
are off it could happen to any of them.

In my report with four columns being totaled, two of which include a tax
calculation the results are sometimes a penny off.

Here are the figures in detail section.

Labor Parts Tax Total
13.38 65.50 5.40 84.28
53.50 5.60 0.46 59.56
6.69 18.45 1.52 26.66
10.00 216.50 17.86 244.36
_____ ______ _____ ______
83.56 306.05 25.25 414.86 Totals in total section


Labor and tax columns are off by a penny.

Looking at the totals to four digits looks like this

83.5625 306.0500 25.2491 414.8616

My formula for Labor and Parts totals is this with the appropriate column
name

=(Sum(nz([LaborCost])) with the vb rounding function it is this

=Round(Sum(nz([LaborCost])),2) the figures still are exactly the same

The formula for tax is the simple (but maybe wrong)
=Sum(nz([PartsTotal]))*0.01*nz([txtTaxRate]) …tax rate is
8.25%

The formula for Total is
=(Sum(nz([LaborCost]))+Sum(nz([partstotal]))+Sum(nz([partstotal]))*nz([txtTaxRate])*0.01

Can anyone help me with getting the totals to show the correct figures. It
looks like a simple answer, but it has eluded me so far. Seems like a simple
rounding with .005 up to the next penny would solve it, but I am not sure how
to accomplish that, if that would indeed work.

From the answers I've seen here I guess I 'd better read the rounding rules
for California to make sure my simple tax calculation doesn't violate rules
(which it probably does).

Any help would be appreciated.
Thanks,

Chuck

 

[Marshall Barton]

Have almost exactly same problem as another recent post with rounding
problems but don’t know which formula to solve it with. Although two totals
are off it could happen to any of them.

In my report with four columns being totaled, two of which include a tax
calculation the results are sometimes a penny off.

Here are the figures in detail section.

Labor Parts Tax Total
13.38 65.50 5.40 84.28
53.50 5.60 0.46 59.56
6.69 18.45 1.52 26.66
10.00 216.50 17.86 244.36
_____ ______ _____ ______
83.56 306.05 25.25 414.86 Totals in total section


Labor and tax columns are off by a penny.

Looking at the totals to four digits looks like this

83.5625 306.0500 25.2491 414.8616

My formula for Labor and Parts totals is this with the appropriate column
name

=(Sum(nz([LaborCost])) with the vb rounding function it is this

=Round(Sum(nz([LaborCost])),2) the figures still are exactly the same

The formula for tax is the simple (but maybe wrong)
=Sum(nz([PartsTotal]))*0.01*nz([txtTaxRate]) …tax rate is
8.25%

The formula for Total is
=(Sum(nz([LaborCost]))+Sum(nz([partstotal]))+Sum(nz([partstotal]))*nz([txtTaxRate])*0.01

Can anyone help me with getting the totals to show the correct figures. It
looks like a simple answer, but it has eluded me so far. Seems like a simple
rounding with .005 up to the next penny would solve it, but I am not sure how
to accomplish that, if that would indeed work.

From the answers I've seen here I guess I 'd better read the rounding rules
for California to make sure my simple tax calculation doesn't violate rules
(which it probably does).

Definitely find out what your taxing body's rules are.
Without that, you could be aiming at the wrong target.

Note that both sets of numbers are "correct", with the Sum
most likely being "more" correct than your pencil and paper
total.

After determining the rules that you must follow, the key to
this kind of issue is to be aware that mathematically:

Round(Sum(x)) is **not** the same as Sum(Round(x))

and that you probably would never have noticed the issue if
you had used the latter (probably less accurate) instead of
the former.

 

[Guest]

Thanks Marsh.

Marsh I agree with you both sets of numbers may be correct but the sum is
likely “more†correct, especially after seeing all figures to four decimal
places.

Regardless of the tax calculation (no answers to a correct method yet) I
think I still have a problem as long as many decimal places are used
internally in calculations and I show individual columns figures to only two
decimal places.

In the example a single roundup in the labor column made the sum appear
wrong since the sum did not require rounding. In the tax column a single
roundup was in the sum by .003 dollars causing the sum to appear a penny off
compared to the individual column figures.

Someone looking at the report would certainly say something is wrong, yet
when I examine all figures to four places the totals are “right†and the
individual amounts (rounded to show two decimals) appear "right" also.

How can this type situation be resolved if calculations are done on many
digits yet individual results are based on one number, and totals based on
the sum of many numbers?

It seems like there always could be a chance of discrepancy if only two
decimal places are shown for the individual figures.

Assuming I resolve the tax calculation, which I will, any other suggestions
on how to make the figures in the individual rows "match" the total of the
column.


Note:
I have been working in Cobol forever and this type of situation has never
occured for me because we use fixed precision in the calculations and results
are usually truncated or shown in both the individual figures and the totals.
What ever occurs internally they always seem to be ok and match.


Thanks,

Charles

Have almost exactly same problem as another recent post with rounding
problems but don’t know which formula to solve it with. Although two totals
are off it could happen to any of them.

In my report with four columns being totaled, two of which include a tax
calculation the results are sometimes a penny off.

Here are the figures in detail section.

Labor Parts Tax Total
13.38 65.50 5.40 84.28
53.50 5.60 0.46 59.56
6.69 18.45 1.52 26.66
10.00 216.50 17.86 244.36
_____ ______ _____ ______
83.56 306.05 25.25 414.86 Totals in total section


Labor and tax columns are off by a penny.

Looking at the totals to four digits looks like this

83.5625 306.0500 25.2491 414.8616

My formula for Labor and Parts totals is this with the appropriate column
name

=(Sum(nz([LaborCost])) with the vb rounding function it is this

=Round(Sum(nz([LaborCost])),2) the figures still are exactly the same

The formula for tax is the simple (but maybe wrong)
=Sum(nz([PartsTotal]))*0.01*nz([txtTaxRate]) …tax rate is
8.25%

The formula for Total is
=(Sum(nz([LaborCost]))+Sum(nz([partstotal]))+Sum(nz([partstotal]))*nz([txtTaxRate])*0.01

Can anyone help me with getting the totals to show the correct figures. It
looks like a simple answer, but it has eluded me so far. Seems like a simple
rounding with .005 up to the next penny would solve it, but I am not sure how
to accomplish that, if that would indeed work.

From the answers I've seen here I guess I 'd better read the rounding rules
for California to make sure my simple tax calculation doesn't violate rules
(which it probably does).

Any help would be appreciated.
Thanks,

Chuck

 

[Marshall Barton]

Marsh I agree with you both sets of numbers may be correct but the sum is
likely “more” correct, especially after seeing all figures to four decimal
places.

Regardless of the tax calculation (no answers to a correct method yet) I
think I still have a problem as long as many decimal places are used
internally in calculations and I show individual columns figures to only two
decimal places.

In the example a single roundup in the labor column made the sum appear
wrong since the sum did not require rounding. In the tax column a single
roundup was in the sum by .003 dollars causing the sum to appear a penny off
compared to the individual column figures.

Someone looking at the report would certainly say something is wrong, yet
when I examine all figures to four places the totals are “right” and the
individual amounts (rounded to show two decimals) appear "right" also.

How can this type situation be resolved if calculations are done on many
digits yet individual results are based on one number, and totals based on
the sum of many numbers?

It seems like there always could be a chance of discrepancy if only two
decimal places are shown for the individual figures.

Assuming I resolve the tax calculation, which I will, any other suggestions
on how to make the figures in the individual rows "match" the total of the
column.

Note:
I have been working in Cobol forever and this type of situation has never
occured for me because we use fixed precision in the calculations and results
are usually truncated or shown in both the individual figures and the totals.
What ever occurs internally they always seem to be ok and match.

Anytime you do calculations with more precision than you
store or display you will be doing some kind of adjustment
on the numbers, even in Cobol. Note that just multiplying a
two decimal place number by another should have four places
in the result, so a fixed place to 2 places may not be
"accurate".

Regardless of all that, you can make the total agree with
the pencil and paper total, even if it is a little less
accurate, by using =Sum(Round(nz(LaborCost),2))

Note that your taxing body may not permit that kind of
calculation. And remember that you must do all calculations
the same way everywhere, even in the cash register.

 

[Guest]

Thanks Marsh.

I had never really thought about the precision in calculations in this
situation until I saw the numbers to many decimal places.

I'll use that formula throughout all the calculations. I'll solve the tax
calculation and do it the "approved" way, if there is one.

I also checked with someone here that handles the user problems and yes
there is the same problem with the Cobol programs we use. Because it is a
lot of money involved the users ignore small discrepencies like what we are
talking about here, but they certainly do occur. Often the calculations are
done in different ways in several programs and each programmer can do them
different ways, so my guess is the results probably are "wrong" quite often
here for reports with hundreds or thousands of money calculations.

Thanks again.

Chuck

 

[Marshall Barton]

I wonder what the accounting and auditor folks think
of all those little "discrepencies" ;-)

Good luck with the tax people. I'll bet the first half
dozen you talk to don't know the rules either. ;-))

Actually, your company accountant can probably
answer the question. Let's hope that the answer
is "it doesn't matter" ;-)))

 

[Guest]

Marsh,

Well apparently the auditors have said nothing or at least nothing ever came
down to our programming department. They are an outside firm and check each
year or two. I asked my manager and project leader about figure
discrepancies and they were the ones that told me yes there were such
discrepancies. They also said the users knew of them and since they were so
"minor" it didn't matter.

As far as my own project I asked your help on, its for a friend of mine and
I looked at his example I am writing a program for (he does books manually!!
and has only a single excel spreadsheet to calculate the totals). All
intermediate calculations are either done by hand or on a hand calculator and
he probably truncates or casually rounds the numbers. He has hundreds of
calculations to get final figures to input into the spreadsheet so there
cannot be any real precision there. His current tax calculations cannot be
following any rules. I just want to make the program does the right thing
especially since I might try to market the program at a later date. Also it
will keep my friend out of trouble with the tax authorities if I follow the
rules.

Thanks again for your help.

Chuck

I wonder what the accounting and auditor folks think
of all those little "discrepencies" ;-)

Good luck with the tax people. I'll bet the first half
dozen you talk to don't know the rules either. ;-))

Actually, your company accountant can probably
answer the question. Let's hope that the answer
is "it doesn't matter" ;-)))
--
Marsh
MVP [MS Access]

I had never really thought about the precision in calculations in this
situation until I saw the numbers to many decimal places.

I'll use that formula throughout all the calculations. I'll solve the tax
calculation and do it the "approved" way, if there is one.

I also checked with someone here that handles the user problems and yes
there is the same problem with the Cobol programs we use. Because it is a
lot of money involved the users ignore small discrepencies like what we are
talking about here, but they certainly do occur. Often the calculations are
done in different ways in several programs and each programmer can do them
different ways, so my guess is the results probably are "wrong" quite often
here for reports with hundreds or thousands of money calculations.