Pennies problem
John
I am calculating tax as follows;
= [Net] * [TaxRate] / 100#
Client complains that there is sometimes difference in pennies in tax
calculation. Is there a better (more precise) way to calculate tax?
Thanks
Regards
CDMAP...@fortunejames.com
Instead of discussing the ways of reducing roundoff error I'll try
calculating the tax the way my favorite waitress does it. If she
charges too much tax she'll get in trouble. If she charges too little
it comes out of her pocket. So she's very careful to get the pennies
correct :-).
Suppose the tax is 8% and the tax table the waitress uses looks
something like:
tblTaxTable
TTID AutoNumber
CentAmount Integer
CentsTax Integer
TTID CentAmount CentsTax
1 0 0
2 12 1
3 25 2
4 37 3
5 50 4
6 62 5
7 75 6
8 87 7
There's a small stack of receipts at the cash register.
tblAmountsToTax
TAID AutoNumber
AmountToTax Currency
TAID AmountToTax
1 $32.52
2 $22.99
Sample Calculation:
Tax on $32.52
32 * 8% + 4 cents on $0.52 from the table = $2.56 + $0.04 = $2.60
since 52 is >= 50 and < 62.
qryCalculateTax:
SELECT 8 AS TaxPct, [AmountToTax], CCur((Int([AmountToTax]) * [TaxPct]
+ (SELECT Last(A.CentsTax) FROM tblTaxTable AS A WHERE A.CentAmount <=
100 * (tblAmountsToTax.AmountToTax -
Int(tblAmountsToTax.AmountToTax)))) / 100) AS theTax FROM
tblAmountsToTax;
!qryCalculateTax:
TaxPct AmountToTax theTax
8 $32.52 $2.60
8 $22.99 $1.83
The query calculates the tax on the dollar amount, then looks up the
remainder of the tax using tblTaxTable via a subquery.
James A. Fortune
CDMAP...@FortuneJames.com
David Harlowe
Round([Net] * [TaxRate] / 100 + .0001 , 2)
The additional .0001 compensates for the round to even functionality
included in the Access Round function. The round to even functionality
causes Access to round .135 to .14 and .145 to .14 instead of the .15 that
you might expect.
Later,
David
"John" <Jo...@nospam.infovis.co.uk> wrote in message
news:7cGdnfWrqua...@pipex.net...
Ron2006
As a point of historical interest, does anyone know how this came to be
called "Bankers' rounding"? I have yet to find any evidence that
bankers have ever rounded in this fashion.
There's a note on Bankers Rounding in this article.
HOWTO: Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q196652
Here's a quick copy...
When you add rounded values together, always rounding .5 in the same
direction results in a bias that grows with the more numbers you add
together. One way to minimize the bias is with banker's rounding.
Banker's rounding rounds .5 up sometimes and down sometimes. The
convention
is to round to the nearest even number, so that both 1.5 and 2.5 round
to 2,
and 3.5 and 4.5 both round to 4. Banker's rounding is symmetric.
Further note:
Access and VBA perform "Bankers Rounding"
Excel does NOT.
Randy Harris
> I found the following:
>
>
>
> As a point of historical interest, does anyone know how this came to be
>
> called "Bankers' rounding"? I have yet to find any evidence that
> bankers have ever rounded in this fashion.
>
I have no idea if there is any truth to it or not, but I heard a long
time ago that some clever programmer in a banking environment found a
way to route all rounding errors into his own bank account. He was
eventually found out and a policy was activated at the institution that
required "bankers rounding" that was intended to prevent someone from
doing it again.
Bankers rounding is practiced in any statistical analysis environment
where a large sample of input data are used. Always rounding x.5 to x+1
would distort the result if many millions of computations were involved.
2.5 is no closer to 3 than it is to 2.
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Ron2006
as bankers rounding. And I heard the same story. We often joked about
how it could be done.
True or not, I have no idea. But never worth it.
Take care.
Ron
Dave Griffiths
Did you find an answer to the problem? I see the discussion diverted a
little. I am curious on the answer for a program I am working on. Would
like to try and avoid the same mistake if there is one.
--
DaveG
Norway - Oslo - Skoyen
Lyle Fairfield
My opinion, having not done this for a long time:
1. be sure all the numbers used in the calculation are of type
currency; do not use any literals as VBA may decide to treat literals
as some type that we may not want them to be treated as.
So the calculation of the original poster whould be expanded to
something like:
Dim Tax as Currency
Dim Net as Currency
Dim TaxRate as Currency
Dim Per as Currency
Net = 12.35
TaxRate = 7
Per = 100
Then we must consider rounding. Many rounding methods use bankers
rounding. That is each number is rounded to its closest even
approximation eg. 1.315 is rounded to 1.32; this looks great until we
learn that 1.325 is ALSO rounded to 1.32. Many (most?) businesses and
tax collectors do not round in this way. They round all halves UP.
If bankers rounding is suitable for you, you can use the VBA round
function.
So
Tax = Round(New * TaxRate / Per, 2)
But if you want the general old rounding most of us learned in grade
school you will need another algorithm.
There are some at
http://www.xbeat.net/vbspeed/c_Round.htm
You could test these for speed and conformance with how you want 1.325
rounded.
If you chose one your calcuation would now look something like this:
Tax = Round02(New * TaxRate / Per, 2)
Another warning: In the olden days when we tried to store a five digit
decimal numeral like 7.23685 as a currency type the rounding to 7.2368
or 7.2369 was erratic. I have read that this has now been corrected but
... I would try to avoid this kind of thing regardless.
Unknown
wrote:
Most states provide a table that shows the sales tax for amounts
between upper and lower amounts. I would recommend that you get this
chart and program it into your system. That would probably cure his
complaint.
Then, of course, you need to update the chart.
Bookreader