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
|
|||
|
|||
Access Functions
Why round(1.125,2) gives 1.12 on Access Functions and
1.13 on Xls functions? |
#2
|
|||
|
|||
Access Functions
Access uses a newer rounding algorthim that is designed to be less biased.
Old systems rounded four values (1,2,3,4) downwards, and five values (5,6,7,8,9) upwards. That approach is biased. The standard procedure to remove the bias is to round to towards the EVEN number when the last digit it a 5. Therefo Round(1.125, 2) rounds down towards 2 (the even number), but: Round(1.135, 2) rounds upwards towards 4 (the even number). At least, that is what Access is supposed to do. Occasionally it does not do that exactly, due to the innate imprecision of floating point numbers. (It does always get it right for Currency type fields, since they are fixed point numbers.) -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Marc" wrote in message ... Why round(1.125,2) gives 1.12 on Access Functions and 1.13 on Xls functions? |
#3
|
|||
|
|||
Access Functions
"Allen Browne" wrote in
. au: Old systems rounded four values (1,2,3,4) downwards, and five values (5,6,7,8,9) upwards. No: there are five numbers that are rounded downwards (0,1,2,3,4). Tim F |
#4
|
|||
|
|||
Access Functions
Not really. The 0 is not rounded!
Thus there is rounding-down action for 4 digits (1, 2, 3, 4) and rounding-up action for 5 digits (5, 6, 7, 8, 9) if 5 is rounded up. Hence the bias occurs if 5 is always rounded up. -- HTH Van T. Dinh MVP (Access) "Tim Ferguson" wrote in message ... No: there are five numbers that are rounded downwards (0,1,2,3,4). Tim F |
#5
|
|||
|
|||
Access Functions
Hello Van:- okay, I'll bite! :-)
"Van T. Dinh" wrote in : No: there are five numbers that are rounded downwards (0,1,2,3,4). Not really. The 0 is not rounded! Yes it is: the way I learned this was as follows:- a number that is _truncated_ to x.0 has to be greater than x, because otherwise it would have been truncated to w.9. Therefore anything that starts with x.0 has to be rounded down to the nearest integer, x. If we are talking about real numbers, of course, there is no such thing as exactly x.0 but only x.00000000000001 and w.999999999999879 and so on. by similar reasoning a number that is _truncated_ to x.5 has to be greater than x.5, and so is rounded up to the nearest integer, y. The problem in computer land is that there appears to be such a thing as _exactly_ x.5 but only because it's the result of something that has already been rounded. Rounding a rounded number is highly unsatisfactory so it really doesn't matter what algorithm is used. I don't know what research has been done here, and I am more than happy to back down if it can be shown that this "even numbers priority" has some kind of advantage over normal rounding, or over "odd numbers priority" or "odd numbers if the day has an S in it..." or something equally arbitrary. Just a thought B Wishes Tim F |
#6
|
|||
|
|||
Access Functions
OK. For simplicity, let assume that we want to round to zero decimal
places, i.e. whole number and data entry / calculations are limited to, says, 2 decimal places. In this case, the fractional portion of the number X to be rounded is 1 out of: 0.00 0.01 0.02 ... 0.49 0.50 0.51 ... 0.98 0.99 The 0.00 is not rounded. Thus we have 99 fractional parts to be rounded. * 0.01 to 0.49 are to be rounded down, i.e. 49 possibilities. * 0.51 to 0.99 are to be rounded up, i.e. 49 possibilities. So the rounding is fairly even at the moment since 49 possibilities are rounded down and 49 possibilities are rounded up. If we always round .50 up, them 49 down and 50 up, hence it becomes biased. Taking it right to the accuracy of, says, 10 digits, we still have the problem of the fractional part of exactly 0.50000 ... even though the bias will be much smaller proportionally (1 out of a billion) but it is still there. However, in reality, 0.5 happens a lot more often than completely random, e.g. in currency. Thus, the bias tends to be magnified. OTOH, rounding always means inaccuracy so this is only a theoretical argument. I don't think it matter that much in reality. -- HTH Van T. Dinh MVP (Access) "Tim Ferguson" wrote in message ... Hello Van:- okay, I'll bite! :-) "Van T. Dinh" wrote in : No: there are five numbers that are rounded downwards (0,1,2,3,4). Not really. The 0 is not rounded! Yes it is: the way I learned this was as follows:- a number that is _truncated_ to x.0 has to be greater than x, because otherwise it would have been truncated to w.9. Therefore anything that starts with x.0 has to be rounded down to the nearest integer, x. If we are talking about real numbers, of course, there is no such thing as exactly x.0 but only x.00000000000001 and w.999999999999879 and so on. by similar reasoning a number that is _truncated_ to x.5 has to be greater than x.5, and so is rounded up to the nearest integer, y. The problem in computer land is that there appears to be such a thing as _exactly_ x.5 but only because it's the result of something that has already been rounded. Rounding a rounded number is highly unsatisfactory so it really doesn't matter what algorithm is used. I don't know what research has been done here, and I am more than happy to back down if it can be shown that this "even numbers priority" has some kind of advantage over normal rounding, or over "odd numbers priority" or "odd numbers if the day has an S in it..." or something equally arbitrary. Just a thought B Wishes Tim F |
#7
|
|||
|
|||
Access Functions
"Van T. Dinh" wrote in
: OTOH, rounding always means inaccuracy so this is only a theoretical argument. I don't think it matter that much in reality. Hear hear. I resign! B Wishes Tim F |
Thread Tools | |
Display Modes | |
|
|