M
Marc
Why round(1.125,2) gives 1.12 on Access Functions and
1.13 on Xls functions?
1.13 on Xls functions?
A
Allen Browne
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. Therefore:
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.)
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. Therefore:
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.)
T
Tim Ferguson
No: there are five numbers that are rounded downwards (0,1,2,3,4).
Tim F
V
Van T. Dinh
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.
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.
T
Tim Ferguson
Hello Van:- okay, I'll bite! 
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
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
V
Van T. Dinh
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)
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)
T
Tim Ferguson
Hear hear. I resign!
B Wishes
Tim F