Rounding issue

[Guest]

I am trying to calculate a value on a form from known information. everything
seems to work properly except with one specific set of data.


ROUND((2.65/8.48)*0.4,2)

The actual non-rounded answer is exactly 0.125, so you would assume that
with the "round" the answer would be 0.13, but for some reason Access is
returning 0.12. This same formula is used on 30,000 other sets of data and in
each of those examples the correct value is returned, but for this one set it
is incorrect. How is Access doing this? What is there something in the logic
of the round function that could be causing this?

 

[sybmathics]

I am trying to calculate a value on a form from known information.
everything
seems to work properly except with one specific set of data.


ROUND((2.65/8.48)*0.4,2)

try the following;

INT((2.65/8.48)*0.4)/100))*100

the rounding problem has to with the fact that computers only recognize
whole numbers (as a matter of fact only 0 and 1).

 

[John Vinson]

What is there something in the logic
of the round function that could be causing this?

Access uses "banker's rounding" - if the value after the round point
is 5, it rounds to the nearest *even* value rather than always
rounding up. This is preferred in financial calculations because the
mean of the rounded values is closer to the mean before rounding; with
the "always round up" approach, the mean of the rounded values will be
larger than the prior mean.

John W. Vinson[MVP]

 

[Guest]

Fascinating. And good to know.

Access uses "banker's rounding" - if the value after the round point
is 5, it rounds to the nearest *even* value rather than always
rounding up. This is preferred in financial calculations because the
mean of the rounded values is closer to the mean before rounding; with
the "always round up" approach, the mean of the rounded values will be
larger than the prior mean.

John W. Vinson[MVP]

 

[James A. Fortune]

try the following;

INT((2.65/8.48)*0.4)/100))*100

the rounding problem has to with the fact that computers only recognize
whole numbers (as a matter of fact only 0 and 1).

I don't think that the binary representation is causing the problem
here, although it's still good to be aware of the binary representation
of numbers. Numbers that end near 5 (on the digit after the rounding
accuracy) from calculations can be coerced easily to end in 5 exactly,
but that would be, IMO, a bad thing if certain calculations are going to
be made later with those numbers. It seems to me that the problem with
always rounding up when the value ends in 5 is that it introduces a bias
if the results are going to be used in, say, another sum or average
because the unidirectional choice causes the sum or average to be
slightly higher than it should be. Also, the more values that end in 5,
the more bias. So not coercing calculations to end in 5 exactly would
eliminate most of the bias. Banker's Rounding seems to be an attempt to
mitigate, but not eliminate that bias when many of the values end
exactly with a 5. If the number of rounds upward is the same as the
number of rounds downward for the numbers ending in .5, then the bias is
eliminated. Because of the Central Limit Theorem in statistics, large
numbers of events with a 50% probability will approach half of the total
number of actual samples. Thus, you can reasonably expect, but not
guarantee, that the bias using Banker's Rounding will approach zero for
a large number of values that end in 5 exactly. In all cases, Banker's
Rounding produces a bias that is less than or equal to the bias produced
by always rounding up numbers that end in 5. Anyway, that's my hypothesis.

James A. Fortune
(e-mail address removed)

 

[Jamie Collins]

Access uses "banker's rounding"

A (sweeping) misstatement.

The ROUND function indeed uses banker's rounding e.g.

SELECT ROUND(-12.345, 2)
returns -12.34 (rounded towards zero because the digit 4 is even).

as do some of the casting functions e.g.

SELECT CLNG(12.5), CLNG(13.5)
returns 12 and 14 respectively.

However, many other functions do not exhibit banker's rounding e.g.

SELECT FORMAT(-12.345, '00.00')
returns '12.35' (rounds away from zero)

SELECT FIX(-12.345)
truncates symmetrically

SELECT INT(-12.345)
truncates asymmetrically

CREATE TABLE Test (
cur_col CURRENCY NOT NULL,
dec_col DECIMAL(19,4) NOT NULL
)
;
INSERT INTO Test VALUES (0.55555, 0.55555)
;
SELECT cur_col, dec_col
FROM Test
;
returns 0.5556 (banker's rounding) and 0.5555 (symmetric truncation)
respectively.

Jamie.

--

 

[John Vinson]

A (sweeping) misstatement.

Sorry. I was responding to the OP's context. You're quite correct of
course.

John W. Vinson[MVP]