percentage calculation...trying again

[Michel Walsh]

That may be due to the overall procedure used.

As example, if the unit price is 1000.01 and the tax rate is 1%:

1000.01 ' price
+ 10.0001 ' tax, full precision
==============
1010.0101 ' total, full precision


Now, for a quantity of 100

- if we round right now before multiplying by the quantity, that makes
1010.01 then, next, multiplying by 100, that ends up with 101 001.00

- instead, if we first multiply by the quantity and only round later:
1010.0101 * 100 = 101001.01 and now, rounding, we end with 101 001.01

so, one penny of difference.

The first formulation (assuming everyone is a decimal data type, for
simplicity) is what we get with:

SUM( quantity * INT(0.5+ unitPrice*taxRate*100))/100

the second formulation is what we get with:

SUM( INT(0.5 + quantity * unitPrice * taxRate ))/100


Since your queries were all like the second formulation, I can only guess
that you compare that result with a procedure which is equivalent to the
first formulation.

Technically, I suppose that the first formulation is what should be use, in
reality. So, it would be to try to rewrite your queries from their actual
formulation, into the first formulation, ie, move the quantity outside the
INT( ) rounding.



Vanderghast, Access MVP

 

[JMS]

I'm Still having problems with this unfortunately. I applied the formulation
using 8.1% sales tax rate you
described below and had no luck getting accurate Calculations on th sales
tax or Gross. What do you think...thanks for your continued help.

Correct Calculations created manually

Gross Sales tax Freight Net
City Tax State Tax
==============================================================
667.38 48.60 18.82 599.96
10.80 37.80
1296.34 92.58 60.82 1142.94
20.57 72.01
4690.88 334.09 232.27 4124.52
74.24 259.84
3224.81 238.95 35.9 2949.96
53.10 185.85
6106.84 452.93 62.15 5591.76
100.65 352.28
---------------------------------------------------------------------------------------------
15986.24 1,167.14 409.96 14,409.14
259.36 907.78



Calculations using SUM( quantity * INT(0.5+ unitPrice*taxRate*100))/100

Gross Sales tax Freight Net
City Tax State Tax
==============================================================
667.38 48.60 599.96
1296.34 92.58 1142.94
4690.92 334.13 4124.52
3224.81 238.95 2949.96
6106.88 452.97 5591.76

 

[Michel Walsh]

The manual computation is probably wrong... because it is too precise! (done
with the help of a hand-held calculator, as example), as under Gross City
Tax, the manual result is 4690.88 and the SUM result is 4690.92.


The calculator can see values to sum as:

12.4545
22.4545
16.4545
------------
51.3635

and then, rounding to the pennies: 51.36


The SUM will round to the pennies before summing:

12.4545 -> 12.45
22.4545 -> 22.45
16.4545 -> 16.45
------------
51.35




so while being MORE precise, the hand held calculation 'is' in error,
because it is more precise, in the circumstance.



Vanderghast, Access MVP

 

[JMS]

Hi Michel:
I actually just did a basic excel spreadsheet which gave me the same
results a calculator would I believe. I can see how the calculation might be
off by $0.01 but I am not sure how in some cases it actually was off by
$0.04 Just trying to make sure that the government gets all their
pennies...seeing as over the course of a year or two they can add up
Just doesn't seem right. Is there a way to create a report that sums a group
of fields in access similar to how excel does it and just do away with the
calculation involving freight and sales tax. Just trying to simplify things
but still give Uncle Sam his stack of pennies

Excel Calculation:
Gross Sales tax Freight Net
================================================
4690.88 334.09 232.27 4124.52
6106.84 452.93 62.15 5591.76


Calculations using SUM( quantity * INT(0.5+ unitPrice*taxRate*100))/100
Gross Sales tax Freight Net
================================================
4690.92 334.13 232.27 4124.52
6106.88 452.97 62.15 5591.76

 

[Michel Walsh]

Excel is like the handheld calculator, it uses 'as much precision' as it
can, even if it SHOWS only a limited amount of information. For Excel, there
is no problem to have $12.4535, ie, 0.35 of a penny, but that just cannot
be, since there is nothing less than a penny. You can get more than a single
penny of difference if you have 4 items:



0.0049
0.0049
0.0049
0.0009
-------------
0.0156

rounded, that gives 0.02. On the other hand, if you round each item and
NEXT, SUM, you get 0.00, so two pennies of difference.



To show that Excel does the same, copy the four number above, FORMAT the
column to display only 2 decimals, you will SEE only 0.00, but the sum will
be 0.02:

real formatted (to see only 2 decimals)
0.0049 0.00
0.0049 0.00
0.0049 0.00
0.0009 0.00
0.0156 0.02

(the last line is the sum). Column B, same numbers as column A, but
FORMATTED, shows that what appears as 0 did sum as 2 pennies. And THAT is
wrong! since too much precision is NOT always what works (here, fractions of
a penny).



Vanderghast, Access MVP

 

[JMS]

I guess it is just a little hard to sell the idea to the Federal Government
that their methods are just too precise, I like mine better I just wish
that we could Sum the line totals without any other calculations other to
add the numbers as the currency end results...hope that makes sense. I am
able to get the precision on the line totals just not the grand total. Can
you think of a way to do that with the Main Report using the Main report
Query in the DB I sent you? Maybe this might be a way around the precision
accuracy issue with Access. Thanks again for your time on this.

 

[Michel Walsh]

I am not in accounting, I cannot even tell if the tax on 100 items is per item or for the whole bunch of 100 items. I know there was, once, a table of taxes, like, for 6%, nothing from 0.00$ to 0.05$, 0.01$ tax from 0.06$ to 0.24$, etc. So, for an item costing 1.06$, the tax is 0.06$ (6% of 1.00) plus 0.01$ (the pennies are from 0.06$ to 0.24$, so, from the table, the tax is 0.01$), for a total of 0.07$ There is no rounding problem since the process was based on a table lookup. As I said, I don't know how tax should be computed if you have 100 items, each sell at 1.06$. Do we compute the tax on each item, then multiply by 100, or do we multiply the cost by 100 and then apply the tax. There CAN be a difference between the two methods, and only your accounting consultant can answer to that problem, given the local applicable law. Once you will get that answer, you would then know WHEN you have to round, and if you have to round BEFORE multiplying by the quantity, then the EXCEL solution, as presented, *is wrong*, since it is equivalent to apply the tax AFTER we multiplied by the quantity.


Note that and Excel, and Access, uses the same mathematical library, and the same processor. To get, from Excel, the same result than with Access, ROUND before summing:

Value =Round(A*, 2)
0.0149 0.01
0.0149 0.01
0.0149 0.01

0.0447 0.03

0.04 0.03


For the first three lines, the first column is the value, the second column is the value rounded (2 decimals).
The fifth line is the sum of the three first lines, and the last line is what we get once we format the previous sum to only show 2 decimals.


Note that the sum in column B is thus:
SUM( ROUND( values, 2) )

same as in Access:
SUM( INT(0.5+100*value)/100 )



To get back the 'full precision' feature, in Access, remove the INT(0.5+ ... * 100)/100

And then, you should get the same result as the first column in Excel.

But from that point, be sure you always use the same technique: do you sum after you rounded, or before. AND remember that FORMATTING does not round anything, internally, it is just a modification on what is displayed, nothing more.



Hoping it may help,
Vanderghast, Access MVP