If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
about rounding calculation
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 |
#2
|
|||
|
|||
about rounding calculation
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...b;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 wrote: 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 |
#3
|
|||
|
|||
about rounding calculation
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" wrote: 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...b;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 wrote: 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 |
#4
|
|||
|
|||
about rounding calculation
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 wrote: 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" wrote: 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...b;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 wrote: 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 |
#5
|
|||
|
|||
about rounding calculation
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" wrote: 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 wrote: 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" wrote: 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...b;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 wrote: 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 |
#6
|
|||
|
|||
about rounding calculation
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 |
#7
|
|||
|
|||
about rounding calculation
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 |
#8
|
|||
|
|||
about rounding calculation
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" wrote: 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 |
#9
|
|||
|
|||
about rounding calculation
That's a new one for me. Hopefully one of the MVPs has perhaps seen
something similar. Similar with a solution............ :-) Ron |
#10
|
|||
|
|||
about rounding calculation
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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
rounding a calculation down to the nearest 0.5 - how? | Suza | General Discussion | 2 | March 29th, 2006 03:09 PM |
Can't Figure Out How To Do This Calculation - PLEASE HELP!! | Pat Hartman\(MVP\) | General Discussion | 0 | January 25th, 2006 05:43 PM |
Variable for holding a calculation for a text box | patentinv | General Discussion | 3 | October 9th, 2005 01:23 PM |
Currency rounding problem | chuckh | Setting Up & Running Reports | 6 | October 7th, 2005 04:55 PM |
rounding numbers in a calculation | KimberlyC | Worksheet Functions | 1 | February 25th, 2004 04:43 PM |