Rounding for Number Data Type

[Guest]

Hello,

I hope someone can explaing the rounding to me, because it doesn't seem to
make much sense to me. I have a field assigned as Number for Data Type. I
change the Field Size to Long Integer and Format to currency with 2 decimal
places. I know that Long Integer doesn't allow fractions or decimal. I'm just
trying to understand the rounding that the system is doing.

With that said, when I enter a value of 4.5, the system rounds it to $4.00.
But if I enter a value of 5.5, it will round it up to $6.00. I don't
understand why the system did not round up for the first value, but do so for
the second one.

Any ideas?

Thanks

 

[Ron2006]

It's called "Bankers Rounding". (I have other words for it but not
printable in polite company)
The following is from a prior post by Wayne Morgan
MS Access MVP

For more info search on "Bankers Rounding"

===========================================================

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. Adding 0.0001 won't fix this
problem,
0.0149 should round down and adding 0.0001 to it will change it to
0.015
which then will round up or down depending on where the even number
is, in
this case it would round to 0.02. Using the other equation, you get
01.49 +
0.5 for 01.99. Taking the Int() you truncate the 1.99 to get 1 then
divide
by 100 to get 0.01. This has rounded down as it should have.

--
Wayne Morgan
MS Access MVP
===============================================

Ron

 

[Guest]

What do you want it to do? If this column actually holds currency values but
you want to allow entry of only whole dollar amounts then probably a better
approach would be make it a Currency data type and set its ValidationRule
property to:

=Int([YourField])

and give it an appropriate ValidationText property.

For all you ever wanted to know about rounding, and a lot more you probably
didn't, have a look at:


http://www.pldesignline.com/howto/showArticle.jhtml?articleID=175801189


To give you a flavours here's what it says about 'banker's rounding':
"Round-half-even: If half-way values are always rounded in the same
direction (for example 3.5 rounds to 4 and 4.5 rounds to 5), the result can
be a bias that grows as more rounding operations are performed. One solution
toward minimizing this bias is to sometimes round up and sometimes round
down.
In the case of the round-half-even algorithm (which is often referred to as
Banker's Rounding because it is commonly used in financial calculations),
half-way values are rounded toward the nearest even number. Thus, 3.5 will
round up to 4 and 4.5 will round down to 4. This algorithm is, by definition,
symmetric for positive and negative values, so both -3.5 and -4.5 will round
to -4.
In the case of data sets that feature a relatively large number of
"half-way" values (financial records provide a good example of this), the
round-half-even algorithm performs significantly better than the
round-half-up scheme in terms of total bias. However, in the case of data
sets containing a relatively small number of "half-way" values – such as
real-world values being applied to DSP algorithms – the overhead involved in
performing the round-half-even algorithm in hardware does not justify its use
(see also the filter examples shown later in this paper)."
Ken Sheridan
Stafford, England