If the decimal part is 0.1 to 0.12, it rounds down to the next lower integer If the decimal part is 0.13 to 0.37 it rounds to 0.25 If the decimal part is 0.38 to 0.62 it rounds to 0.5 If the decimal part is 0.63 to 0.87 it rounds to 0.75 If the decimal part is 0.88 or more, it rounds up to the next higher integer
views:
61answers:
3In Jet/ACE and VBA the Round() function uses banker's rounding. Does this have an unexpected effect on the results? Also, what does one expect for negative numbers, and does Jet/ACE and VBA's handling of negative numbers match those expectations?
David-W-Fenton
2010-05-25 18:33:14
Banker's rounding should be fine. If you have -1.125, 4 times that is -4.5 which rounds to -4, which becomes -1. -1.375 rounds the other way, up to -1.5. They alternate, which is what banker's rounding is supposed to accomplish. 0.375 rounding UP to 0.5 is balanced by 0.625 rounding DOWN to 0.5. 0.875 rounding UP to 1.0 is balanced by 1.125 rounding DOWN to 1.0. It's not even an issue if you are talking about multiples of 0.01 (cents).
Vagrant
2010-05-26 19:29:20
In certain scenarios (e.g., a credit memo), you don't want -4.5 to round to -4 but to -5. Access/Jet/ACE rounding rounds up on the number line (in the positive direction), rather than on the absolute quantity involved, and you have to be careful with that (hence my comment).
David-W-Fenton
2010-05-26 22:26:33
@Daid-W-Fenton Since which version? "The Round function performs round to even, which is different from round to larger." -- http://msdn.microsoft.com/en-us/library/se6f2zfx.aspx Debug.Print Round(19.955, 2) 'Answer: 19.95 Debug.Print Format(19.955, "#.00") 'Answer: 19.96
Remou
2010-05-27 08:06:14
OK, either way, you need to understand that you're not getting what you necessarily expect.
David-W-Fenton
2010-05-27 17:51:19
+3
A:
There is a general method for this:
- Multiply your number by 4.
- Round to the nearest integer.
- Divide by 4.
In SQL:
ROUND(column * 4) / 4
Mark Byers
2010-05-24 21:50:32
A:
I don't know the exact function name, but basically you use floor(4*x)/4. floor might be called int, to_int, or something like that.
Adam Crume
2010-05-24 21:50:47
Wrong answer aside, but which versions of Access implement Floor()?
David-W-Fenton
2010-05-25 18:23:42