about rounding calculation

[fox]

Hi!
I have a question about rounding. In my report, it show up 3 digital.
However, the total field only need to be show 2 digital. So I use Total =
Round(A,3) + Round(B,3), but the result I got is still not right. How should
I change it?

Original A=0.22152, B=0.333333
Display A=0.222, B=0.333
Total I want = 0.222+0.333 = 0.56
But the result I got = 0.22152 + 0.333333 = 0.554853 = 0.55

Thank you.

fox

 

[Ron2006]

Fox, I believe you have fallen victim to the dreaded "Bankers
Rounding" Syndrome.....

See the following MS article


MS products' rounding explained:
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652


========================

Further comments:

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.

--
Wayne Morgan
MS Access MVP

===========================

Ron

 

[fox]

Thank you for the info.
I think it's a little different, and I think I miss some details.
I use the code in VBA of the report.
Total = Round(A,3)+Round(B,3)
which show the same result in the report as
Total = A + B

Is anyway to make it add the rounded number?
(not round after adding)

fox

 

[Ron2006]

I believe that the problem is that the number you are seeing is not the
number that is actually present there.

Make sure that the query is a Select Query or sum but run it separate
from the report and then actually put your cursor in the field. I am
willing to be that the number that now shows is NOT the number as you
are seeing and that it is actually smaller than the number that is
normally visible. When you add the numbers you get a different result
because you are seeing the addition of the underlying numbers NOT the
addition of the visible display numbers.

Ron

 

[fox]

I check the number value Immediate window with running my code, it's weird.
A = 0.31217999
Round(A,3) = 0.312
B = 0.21299999
Round(B,3) = 0.21299999
which mean for some reason B is not rounded.
Do you know why casue this? Banker's rounding?

Thank you.

fox

 

[Ron2006]

Yes......... Banker's rounding is the culprit/cause/curse

even "rounds" down and not up.....

You may want to do a search in this NG on Banker's rounding to see if
someone suggested a real solution/workaround to this. adding .001 was
one suggest solution but I saw a post where it showed that this DID NOT
solve the problem

I would have to think about a possible solution AFTER doing a search
about the problem.

Ron

 

[Ron2006]

Is this actual VBA code in a module - looking at each record one at a
time - that you are running for this or simply part of a query?

Ron

 

[fox]

The code is in VBA(report),
Me.Total.Value=Round(A,3) + Round(B,3)

I add Temp1/Temp2 to check the value with Immediate windows
Temp1=Round(A,3)
Temp2=Round(B,3)

Temp1 with A rounded correctly, gave me 0.312, but
Temp2 with B won't round, gave me 0.212999993
And the Total value = 0.524999993
I try to switch A & B, it still won't round the value 0.212999993

But if I type ?Round(0.212999993, 3) in Imediate window,
it gave me 0.213 correctly. Is this some kind of function limited?

Thank you.

fox

 

[Ron2006]

That's a new one for me. Hopefully one of the MVPs has perhaps seen
something similar.

Similar with a solution............

Ron

 

[Ron2006]

I just saw some references to using DECIMAL format that implied that
there is truncation involved with it.

Maybe changing the field format in the db to Decimal with 3 decimal
places and then doing the rounding that you are suggesting will get you
what you want.

Ron