Pennies problem

[John]

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

 

[Wayne Morgan]

What sort of variable are you assigning this to or is it the Control Source
of a textbox? Is the textbox limited to 2 decimals? If so, then Access will
round to the 2nd decimal place. It may not round the way you want, you may
be better off doing the rounding yourself in the equation. Also, if you add
up several of these answers, what will be added is the actual answer to how
ever many decimal places there are, not the displayed answer with 2 decimal
places. Rounding the answer yourself before doing the addition would remove
that problem.

 

[John]

What would be a good way to round to 2 digits myself?

Thanks

Regards

What sort of variable are you assigning this to or is it the Control
Source of a textbox? Is the textbox limited to 2 decimals? If so, then
Access will round to the 2nd decimal place. It may not round the way you
want, you may be better off doing the rounding yourself in the equation.
Also, if you add up several of these answers, what will be added is the
actual answer to how ever many decimal places there are, not the displayed
answer with 2 decimal places. Rounding the answer yourself before doing
the addition would remove that problem.

--
Wayne Morgan
MS Access MVP

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

 

[James A. Fortune]

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

Too bad I didn't see sooner that this was multiposted to
comp.databases.ms-access. I could have avoided giving this answer:

http://groups.google.com/group/comp.databases.ms-access/msg/24b62fc73ccf04d8?hl=en&

James A. Fortune
(e-mail address removed)

 

[Guest]

Try these:

= (INT (( [Your Equation] ) * 100)) / 100

= (INT (( [Your Equation] ) * 100) + 1) / 100

Your tax calculation results in dollars and cents, and extra decimals.
Multiply that by 100 moves the decimal point two places to the right.
The INT function truncates any remaining decimal places.
Dividing all that by 100 leaves you with dollars and only 2 decimals of cents.

The "plus 1" above is sometimes used to assist rounding.

What would be a good way to round to 2 digits myself?

Thanks

Regards

What sort of variable are you assigning this to or is it the Control
Source of a textbox? Is the textbox limited to 2 decimals? If so, then
Access will round to the 2nd decimal place. It may not round the way you
want, you may be better off doing the rounding yourself in the equation.
Also, if you add up several of these answers, what will be added is the
actual answer to how ever many decimal places there are, not the displayed
answer with 2 decimal places. Rounding the answer yourself before doing
the addition would remove that problem.

--
Wayne Morgan
MS Access MVP

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

 

[Wayne Morgan]

Try
= Int(NumberToRound * 100) + 0.5) / 100

Adding the 0.5 will round up any results where the 3rd decimal place is 5 or
larger. This is the way sales tax is usually rounded and calculated.

--
Wayne Morgan
MS Access MVP

What would be a good way to round to 2 digits myself?

Thanks

Regards

What sort of variable are you assigning this to or is it the Control
Source of a textbox? Is the textbox limited to 2 decimals? If so, then
Access will round to the 2nd decimal place. It may not round the way you
want, you may be better off doing the rounding yourself in the equation.
Also, if you add up several of these answers, what will be added is the
actual answer to how ever many decimal places there are, not the
displayed answer with 2 decimal places. Rounding the answer yourself
before doing the addition would remove that problem.

--
Wayne Morgan
MS Access MVP

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

 

[John]

Would Round([Net] * [TaxRate] / 100 + .0001 , 2) do the same?

Regards

Try
= Int(NumberToRound * 100) + 0.5) / 100

Adding the 0.5 will round up any results where the 3rd decimal place is 5
or larger. This is the way sales tax is usually rounded and calculated.

--
Wayne Morgan
MS Access MVP

What would be a good way to round to 2 digits myself?

Thanks

Regards

What sort of variable are you assigning this to or is it the Control
Source of a textbox? Is the textbox limited to 2 decimals? If so, then
Access will round to the 2nd decimal place. It may not round the way you
want, you may be better off doing the rounding yourself in the equation.
Also, if you add up several of these answers, what will be added is the
actual answer to how ever many decimal places there are, not the
displayed answer with 2 decimal places. Rounding the answer yourself
before doing the addition would remove that problem.

--
Wayne Morgan
MS Access MVP


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

 

[Edward Reid]

If you are doing any financial calculations where the pennies matter,
then you MUST store your amounts in integers -- in other words, the
number of pennies as an integer.

If you don't understand why -- and based on your question, you probably
don't -- then you shouldn't be coding calculations where the pennies
matter.

The complete answer is a good part of a book, at least a chapter or
two, not a newsgroup posting.

Edward

 

[Wayne Morgan]

The built-in Round() function does "banker's" or "scientific" rounding. It
will take .5 and round it to the nearest even number. This reduces the
rounding error in calculations. 1 number doesn't move (.0), 4 round down
(.1 - .4), 4 round up (.6 - .9), and one goes up half the time and down half
the time (.5). However, this isn't the way most folks round and isn't the
way sales tax is usually calculated. Instead, these are usually calculated
by rounding .5 up all of the time. Adding 0.0001 won't fix this problem,
0.0149 should round down and adding 0.0001 to it will change it to 0.015
which then will round up or down depending on where the even number is, in
this case it would round to 0.02. Using the other equation, you get 01.49 +
0.5 for 01.99. Taking the Int() you truncate the 1.99 to get 1 then divide
by 100 to get 0.01. This has rounded down as it should have.

 

[John]

You got two right brackets but only one left. Which one of the two right
ones is redundant?

Thanks

Regards

Try
= Int(NumberToRound * 100) + 0.5) / 100

Adding the 0.5 will round up any results where the 3rd decimal place is 5
or larger. This is the way sales tax is usually rounded and calculated.

--
Wayne Morgan
MS Access MVP

What would be a good way to round to 2 digits myself?

Thanks

Regards

What sort of variable are you assigning this to or is it the Control
Source of a textbox? Is the textbox limited to 2 decimals? If so, then
Access will round to the 2nd decimal place. It may not round the way you
want, you may be better off doing the rounding yourself in the equation.
Also, if you add up several of these answers, what will be added is the
actual answer to how ever many decimal places there are, not the
displayed answer with 2 decimal places. Rounding the answer yourself
before doing the addition would remove that problem.

--
Wayne Morgan
MS Access MVP


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

 

[Wayne Morgan]

Oops, sorry.

= Int((NumberToRound * 100) + 0.5) / 100

Technically, the inner set is redundant since multiplication will be done
before addition.