Round function question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

ANS: Round([data],1)

we found that the above function in Access does not always give a correct
rounded figure, where we suppose 0.4 is dropped and 0.5 is rounded up. The
problem is that it sometimes works but sometimes not, and we do not know why,
and do not know under what conditions it will work. Is this a small error in
Access ? Anyone has idea on how to solve this ? Thanks in advance.
 
Kanny,

This is probably something that has to do with limited calculation accuracy,
imposed by the representation of decimals in binary in the heart of the
computer. There was another posting on the subject three months ago, looking
into which I found the following example which demonstrates this kind of
error (copying from my posting back then):

In simple math, 165* 0.175 = 28.875; this number rounded to two decimals
equals 28.88. Now try this...
in the immediate window:
?round(28.875, 2)
returns 28.88 (correct)

then:
?round(165 * 0.175, 2)
returns 28.87 (wrong)

Then try:
?format(165 * 0.175 - 28.875, "0.000000000000000000000000000000")
returns -0.000000000000001831867990631510 !!!
which means that Access calculates 165*.175 as something short of 28.875,
only you need 15 decimals before you can see it!

HTH,
Nikos
 
Access/VB now uses 'bankers rounding'. That is, even numbers
round down, odd numbers round up:

round(1.4) = 1
round(1.5) = 2
round(1.6) = 2
....
round(2.4) = 2
round(2.5) = 2
round(2.6) = 3

This is good for finance and statistics, but is not what many
people learned in grade school. If this is your problem, it
is probably ok to just learn to live with it.

You may also have problems because the numbers you see may
not be exactly the number you have: Access rounds numbers for
display, so although you may think you have 0.5, you may
actually have 0.4999999999.

Also, you may have problems because of the way you have used
floating point numbers. Binary Floating point numbers can't
be used exactly for numbers like 3.125.

However, if you understand Bankers Rounding and Floating
Point numbers, you can still have problems with rounding
in the rounding calculations used by Access/VB/Windows. If
you know that this is your problem, then you need to use your
own rounding function, like this one:

Modules: Implementing a custom Rounding procedure
http://www.mvps.org/access/modules/mdl0054.htm

Note that it does bankers rounding also - most people who
really care about rounding prefer bankers rounding.

(david)
 
Back
Top