Jim Lawrence
accessd at shaw.ca
Wed Jan 25 09:24:47 CST 2006
Hi Scott:
There must be a round off error somewhere in the process. As I am just
finishing off an accounting system this problem has always been apparent.
There are a few ways that accounting applications resolve the round off
error.
1. Store all values as integers and perform any math using those integers.
Example (assuming 6 significant places are required.)
Value 0.884746 would become 884746 with a mantissa of 6. All subsequent
calculations are performed in integer math or logs are used. (Adding instead
of multiple and subtract instead of dividing.)
2. In the case of a multiplication or division being required the
calculation is performed once and then the values are managed again as
integers. This is particularly true when applying taxes to invoicing.
Example (given that the state or provincial tax is 6.5% and the federal tax
is 7.0%.)
The taxes are applied immediately then added from there on. In appropriate
tax is generated per item:
Given:
Subtotal PST GST Displayed/Saved Total
123.56 8.0314 8.6492
8.03 8.65 140.24
456.23 29.65495 31.9361
29.66 31.94 61.60
+
579.79 +37.69 + 40.59 =658.07 right-way
579.79 * 1.135% (total tax) =658.06 wrong-way
When tax totals are generated then all the tax subtotals are just added
together. In theory, all the subtotals could be added and the total tax
could be calculated but the answer would be wrong. This is the same problem
experience when trying to calculate the average by item and then expecting
the average for the whole group to add up. It never will.
HTH
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Scott Marcus
Sent: January 25, 2006 4:51 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] bust out the math books
Mathematical answer...
C1 = Total
C2 = Sample Size
C3 = Average
C1 C2 C3
===========
A B A/B
C D C/D
E F E/F
G H G/H
I J I/J
Real average = (A+C+E+G+I)/BDFHJ
Average of averages = (A/B + C/D + E/F + G/H + I/J)/5
Which equals...
((ADFHJ+CBFHJ+EBDHJ+GBDFJ+IBDFH)/(BDFHJ))/5
Which equals...
(ADFHJ+CBFHJ+EBDHJ+GBDFJ+IBDFH)/5BDFHJ
(A+C+E+G+I)/BDFHJ does not equal
(ADFHJ+CBFHJ+EBDHJ+GBDFJ+IBDFH)/5BDFHJ
Scott Marcus
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Francisco
Tapia
Sent: Tuesday, January 24, 2006 7:50 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] bust out the math books
can someone answer me this:
C1 C2
C3 1566 1770
0.884746 5130 5340
0.960674 1320 1800
0.733333 6440 7280
0.884615 1770 1800
0.983333
16226 17990
0.901946
If I take C1 / C2 I get C3... there must be a mathimaticall reason as to
why
I can't just average column C3 to get the 90%, instead I end up with
88.93%
any thoughts?
--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
NOTICE: This electronic mail transmission is for the use of the named
individual or entity to which it is directed and may contain information
that is privileged or confidential. If you are not the intended recipient,
any disclosure, copying, distribution or use of the contents of any
information contained herein is prohibited. If you have received this
electronic mail transmission in error, delete it from your system without
copying or forwarding it, and notify the sender of the error by replying via
email or calling TSS Technologies at (513) 772-7000, so that our address
record can be corrected. Any information included in this email is provided
on an as is and where as basis, and TSS Technologies makes no
representations or warranties of any kind with respect to the completeness
or accuracy of the information contained in this email.