Truncating Numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm creating an accounting database.

I'm running into trouble when I calculate totals such as the amount I owe
on several bills.

The system stores data to several decimal places and calculates totals based
on these amounts rather than the rounded amounts (to 2 decimal places) that
are acutally displayed. Example:

Displayed Stored in System
Add this $ 2.00 $ 1.995

Plus this $ 2.00 $ 1.995

Totals this $ 3.99 $ 3.99

Does anyone know how can I get the system to store all calculated monetary
fields to 2 decimal places, rounded. Or another way to resolve this issue?

Thanks




I have all monetary fields set to the "Currency" Data Type.

Most invoices consist of a units * price
 
FJquestioner said:
I'm creating an accounting database.

I'm running into trouble when I calculate totals such as the amount I owe
on several bills.

The system stores data to several decimal places and calculates totals
based
on these amounts rather than the rounded amounts (to 2 decimal places)
that
are acutally displayed. Example:

Displayed Stored in System
Add this $ 2.00 $ 1.995

Plus this $ 2.00 $ 1.995

Totals this $ 3.99 $ 3.99

Does anyone know how can I get the system to store all calculated monetary
fields to 2 decimal places, rounded. Or another way to resolve this
issue?

Thanks




I have all monetary fields set to the "Currency" Data Type.

Most invoices consist of a units * price
Click to expand...

I have a program that keeps track
of tax rates for individual cities and
counties in our state, and when we
sell a product other than "in-store",
we have to apply the appropriate tax
for the city/county where that product
will be sent.

At certain intervals, we have to provide
the state with a report that sums these
values "across and down."

Even though these values (pSale*pTax) are
converted to Currency, they still contain
4 digits to the right of the decimal.

So, we might see $12.35 (formatted to
2 decimals), but the number is still 12.3546.

When we sum them "across and down" those
extra 2 digits at the end can cause the "across" sums
to not match up with "down" sums.

So, when we compute

Ccur(pSale*pTax)

we want the 2 ending digits to always be "00"

There are 2 methods to round the computation.

1) Ccur(Round(pSale*pTax, 2))
con:
the Round function uses Banker's Rounding
so it will round to the nearest even number
in the specific case of $x.xx50

for example,
Ccur(Round(1.2850, 2)) = 1.2800

BUT...we wanted it to round 1.2850 up to 1.2900

2) to round to $x.xx00 and to always round $x.xx50 UP,
we use a formula such as:

CCur(Int((pSale * pTax) / 0.01 + 0.5) * 0.01)

Examples from Immediate Window:
(actually, the results in the window won't
show ending zeroes, but I have added them
to show what result would be if we had formatted
the result to "#.0000")

pSale=CCur(64.25)
pTax=0.02
?pSale*pTax
1.285
?CCur(Round(pSale*pTax, 2))
1.2800
?CCur(Int((pSale * pTax) / 0.01 + 0.5) * 0.01)
1.2900

pSale=CCur(64.75)
pTax=0.02
?pSale*pTax
1.295
?CCur(Round(pSale*pTax, 2))
1.3000
?CCur(Int((pSale * pTax) / 0.01 + 0.5) * 0.01)
1.3000
 
Thanks a lot Gary for the quick and helpful solution. I tried it on a couple
of formulas and it worked like a charm.

Much appreciated.
 
you're welcome -- I learned it here myself....

actually, I was afraid I might confuse
things by using an example tax rate of .02 ....

how could you trust someone's formula
that uses a rate that fails miserably to meet any
self-respecting tax authority's "gouge threshold?"

8-)
 
Back
Top