RE: best practices.rounding

Adding or subtracting individual base values will not result in any
discrepancy with manual addition or subtraction when a Currency data type is
employed. Where differences can arise is when computations involving
multiplication or division are done on those values.

Say you have a list of items supplied to a customer to whom you give a
discount. Doing this manually many people would calculate the net price per
item rounded to two decimal places, then add the rounded values to give the
total net price. When Access computes the net values for each item supplied,
however, it rounds the result more precisely to four decimal places. The
total net price produced from the summation of these values might differ from
the manually calculated total net price. The total net price produced by
Access is in most circumstances the more correct one as, by virtue of the
greater precision at the individual transaction level the cumulative rounding
errors which result from manually rounding to two decimal places at each
transaction level are suppressed, which is exactly what the Currency data
type is designed to do.

To many people who have been familiar with the manual approach of rounding
per transaction the result produced by Access seems erroneous as the total
can differ from the sum of the individually rounded amounts. Neither can be
said to be right or wrong, however; it all depends on how its wished to be
done in particular business models. Many small businesses prefer computed
values which mimic the manually calculated ones. It should be noted,
however, that when dealing in large currency amounts the result of cumulative
rounding errors can be significant in terms of monies lost or gained. This
is why rounding methods aimed at balancing out the errors by rounding up or
down in equal proportions were commonly used, the basis of this being that
over a large number of transactions the proportion in each case should
approach 50 per cent, but this does not in itself allow for large variations
in the individual amounts; if the transactions rounded up are mostly large
amounts and the ones rounded down are mostly small amounts there will be a
significant cumulative error even if exactly half round up and half round
down. Again the assumption was that over a large number of transactions the
this would balance itself out and any cumulative error would be insignificant.

For an overview of the many different rounding algorithms see:


http://www.pldesignline.com/howto/showArticle.jhtml?articleID=175801189


Ken Sheridan
Stafford, England

"Josh" wrote:

I understand why Acess rounds the way it does (at least somewhat), and "bankers
rounding", so I'm not asking the basic rounding question. (just one level up
from basic ;-)

I've done rounding two ways, either Currency for both actual currency values and
and Double Number values. I've also used MS's SymArith function. (included at
end of this post, just in case anyone wondered what the function was.)

I'm wondering what the 'best practice' way is. Do you simply use Currency for
Currency, and not round to 2 decimal places. If so, then someone adding figures
up and comparing could come up with something different, because doesn't Access
store 4 digit for currency, which could have a slightly different totaled amount
than someone adding with excel, for example.

I know some people used to say to just use currency for numbers that are not
actual currency, and just format the Form or Report to not show the $ sign. That
works, but wouldn't the SymArith function be better?

Like I said, I'm just wondering what the best practice is. The few DB's I've
done work just fine, just wondering what the pros do.

Thanks, Josh

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function



.

Relevant Pages

  • Re: best practices.rounding
    ... And, if using Currency, whether for actual currency or non-currency situations, ... greater precision at the individual transaction level the cumulative rounding ... can differ from the sum of the individually rounded amounts. ...
    (microsoft.public.access.gettingstarted)
  • Re: Anyone had different results between Access calculations and E
    ... professionally and am aware of the rounding issues. ... my calculations and try to have closer control over the calculating field ... Dim MyNumber As Single ... If that is the case, then use a currency field, NOT ...
    (microsoft.public.access.reports)
  • Re: Pennies problem
    ... Client complains that there is sometimes difference in pennies in tax ... Dim Tax as Currency ... Then we must consider rounding. ...
    (comp.databases.ms-access)
  • Re: Sum not totalling correctly
    ... At what point do I need to perform the rounding? ... > The Currency data type stores the value to 4 decimal places, ... Create a query into this table. ... >>> where Amount is the name of the Currency field you are trying to Sum. ...
    (microsoft.public.access.forms)
  • Re: need help with a database
    ... You said that you already have tables for Senders and Beneficiaries. ... the current exchange rate from the given currency to your base ... [note that this belongs here only if the commission rate is directly ... your transaction table needs the following fields: ...
    (microsoft.public.access.tablesdbdesign)
Loading