Dave Griffiths wrote:
Hi John
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
John wrote:
Hi
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
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.