round(0.525,2) = 0.52, why?

[TC]

Read-up on "banker's rounding".

HTH,
TC

Dear all,

I'd created a very simple database with only one table. In this table, it

only contains 1 field with Field Size = double and Decimal = Auto. I'm tried
to key-in some numbers like 0.515, 0.525,0.535 and 0.565 into this field.
When I retrieve this data using a query with round to 2 decimal point, the
query gave me these result as below:

0.515 = 0.52 (correct ans)
0.525 = 0.52 (wrong ans)
0.535 = 0.54 (correct ans)
0.565 = 0.56 (wrong ans)
May I know why the round function give me wrong result? how to resolved

it? Is there any others function that I can use beside ROUND?

 

[Fion Hu]

Dear all

I'd created a very simple database with only one table. In this table, it only contains 1 field with Field Size = double and Decimal = Auto. I'm tried to key-in some numbers like 0.515, 0.525,0.535 and 0.565 into this field. When I retrieve this data using a query with round to 2 decimal point, the query gave me these result as below
0.515 = 0.52 (correct ans
0.525 = 0.52 (wrong ans
0.535 = 0.54 (correct ans
0.565 = 0.56 (wrong ans
May I know why the round function give me wrong result? how to resolved it? Is there any others function that I can use beside ROUND

Thank you

Best regards
Fion Hu

 

[Ken Snell]

The round function is designed to round down when the last digit being kept
is even (0.525 goes to 0.52 because 2 is event). That is the way it's
designed....if you want a different behavior, one way to "fool" the function
is to add 0.001 to each number before you round it.

--
Ken Snell
<MS ACCESS MVP>

Dear all,

I'd created a very simple database with only one table. In this table, it

only contains 1 field with Field Size = double and Decimal = Auto. I'm tried
to key-in some numbers like 0.515, 0.525,0.535 and 0.565 into this field.
When I retrieve this data using a query with round to 2 decimal point, the
query gave me these result as below:

0.515 = 0.52 (correct ans)
0.525 = 0.52 (wrong ans)
0.535 = 0.54 (correct ans)
0.565 = 0.56 (wrong ans)
May I know why the round function give me wrong result? how to resolved

it? Is there any others function that I can use beside ROUND?

 

[Fion Hu]

Dear all,

Thanks for you reply. I still can't understand that 0.525 goes to 0.52 is because of 2 is event but why 0.545 = 0.55. 0.575 = 0.57?

Thank you.
Fion Hu
----- Ken Snell wrote: -----

The round function is designed to round down when the last digit being kept
is even (0.525 goes to 0.52 because 2 is event). That is the way it's
designed....if you want a different behavior, one way to "fool" the function
is to add 0.001 to each number before you round it.

--
Ken Snell
<MS ACCESS MVP>

Dear all,

only contains 1 field with Field Size = double and Decimal = Auto. I'm tried
to key-in some numbers like 0.515, 0.525,0.535 and 0.565 into this field.
When I retrieve this data using a query with round to 2 decimal point, the
query gave me these result as below:

0.515 = 0.52 (correct ans)
0.525 = 0.52 (wrong ans)
0.535 = 0.54 (correct ans)
0.565 = 0.56 (wrong ans)
May I know why the round function give me wrong result? how to resolved

it? Is there any others function that I can use beside ROUND?

 

[Tim Ferguson]

Thanks for you reply. I still can't understand that 0.525 goes to 0.52
is because of 2 is event but why 0.545 = 0.55. 0.575 = 0.57?

Mathematician's rounding depends on the fact that there is no such thing as
an exact number: so 3.500000000 is likely to be short for

2.5000000000000000000000000000000000000000000000000000000000000002

and it therefore rounds to the nearest integer 3. If it was actually nearer
to 2, it would have had to be

2.4999999999999999999999999999999999999999999999999999999999999998

and there would be no argument.

Unfortunately, in computer land, there are exact numbers, and

2.5000000000000000000000000000000000000000000000000000000000000000

really does exist, so there has to be an arbitrary rule about whether this
jumps to 2 or 3. The bankers' rule says that all ties of this nature fall
toward an even number, so 2.5 -> 2 but 3.5 -> 4. On average, this is said
to be fairer in some sense, although I confess I cannot see it. Then again,
I am mathematically trained and don't have enough money to bother the
bankers.

If you want to use mathematicians' rounding, it is easy enough with a
funciton like

= INT(dblSomeNum + 0.5)

or

= INT(dblSomeNum * 100 + 0.5) / 100.0

if you want rounding to two dp.



Hope that helps


Tim F

 

[TC]

Read-up on "bankers' rounding"!

TC

Dear all,

Thanks for you reply. I still can't understand that 0.525 goes to 0.52 is

because of 2 is event but why 0.545 = 0.55. 0.575 = 0.57?

 

[Fion Hu]

Dear all,

Thank you very much for your help. It's works!!!
So, now I can explain to my boss.

Best regards,
Fion Hu

----- Tim Ferguson wrote: -----

is because of 2 is event but why 0.545 = 0.55. 0.575 = 0.57?

Mathematician's rounding depends on the fact that there is no such thing as
an exact number: so 3.500000000 is likely to be short for

2.5000000000000000000000000000000000000000000000000000000000000002

and it therefore rounds to the nearest integer 3. If it was actually nearer
to 2, it would have had to be

2.4999999999999999999999999999999999999999999999999999999999999998

and there would be no argument.

Unfortunately, in computer land, there are exact numbers, and

2.5000000000000000000000000000000000000000000000000000000000000000

really does exist, so there has to be an arbitrary rule about whether this
jumps to 2 or 3. The bankers' rule says that all ties of this nature fall
toward an even number, so 2.5 -> 2 but 3.5 -> 4. On average, this is said
to be fairer in some sense, although I confess I cannot see it. Then again,
I am mathematically trained and don't have enough money to bother the
bankers.

If you want to use mathematicians' rounding, it is easy enough with a
funciton like

= INT(dblSomeNum + 0.5)

or

= INT(dblSomeNum * 100 + 0.5) / 100.0

if you want rounding to two dp.



Hope that helps


Tim F

 

[Fion Hu]

Dear TC,

May I know where to find info about "Bankers' rounding"?

Best regards,
Fion Hu

----- TC wrote: -----

Read-up on "bankers' rounding"!

TC

 

[Mike Sherrill]

On Wed, 10 Dec 2003 09:16:56 -0800, Tim Ferguson

[snip]

The bankers' rule says that all ties of this nature fall
toward an even number, so 2.5 -> 2 but 3.5 -> 4. On average, this is said
to be fairer in some sense, although I confess I cannot see it.

[snip]

"Drift" describes what happens when you repeatedly add and subtract
numbers that are close together. Here's what happens if you start
with 1, and repeatedly subtract and add 0.455, arithmetically rounding
(elementary school rounding) each result to two places.

1.00 + 0.455 = 1.455, rounds to 1.46
1.46 - 0.455 = 1.005, rounds to 1.01
1.01 + 0.455 = 1.465, rounds to 1.47
1.47 - 0.455 = 1.015, rounds to 1.02

The result drifts away from the original value. Here's what happens
if you do the same thing, rounding instead to the nearest even number
("banker's rounding").

1.00 + 0.455 = 1.455, rounds to 1.46
1.46 - 0.455 = 1.005, rounds to 1.00
1.00 + 0.455 = 1.455, rounds to 1.46
1.46 - 0.455 = 1.005, rounds to 1.00

 

[Fion Hu]

Dear Mike

Thanks for your reply

Best regards
Fion H

----- Mike Sherrill wrote: ----

On Wed, 10 Dec 2003 09:16:56 -0800, Tim Ferguso

[snip

The bankers' rule says that all ties of this nature fall
toward an even number, so 2.5 -> 2 but 3.5 -> 4. On average, this is said
to be fairer in some sense, although I confess I cannot see it.

[snip

"Drift" describes what happens when you repeatedly add and subtrac
numbers that are close together. Here's what happens if you star
with 1, and repeatedly subtract and add 0.455, arithmetically roundin
(elementary school rounding) each result to two places

1.00 + 0.455 = 1.455, rounds to 1.4
1.46 - 0.455 = 1.005, rounds to 1.0
1.01 + 0.455 = 1.465, rounds to 1.4
1.47 - 0.455 = 1.015, rounds to 1.0

The result drifts away from the original value. Here's what happen
if you do the same thing, rounding instead to the nearest even numbe
("banker's rounding")

1.00 + 0.455 = 1.455, rounds to 1.4
1.46 - 0.455 = 1.005, rounds to 1.00
1.00 + 0.455 = 1.455, rounds to 1.4
1.46 - 0.455 = 1.005, rounds to 1.00

 

[TC]

Hi Fion

Just do a search using any web search engine. For example:
1. go to www.google.com
2. type "bankers rounding" (with or without the quotes) and press return.

HTH,
TC

 

[Fion Hu]

Dear TC

Thank you

Best regards
Fion H

----- TC wrote: ----

Hi Fio

Just do a search using any web search engine. For example
1. go to www.google.co
2. type "bankers rounding" (with or without the quotes) and press return

HTH
T

 

[Fion Hu]

Dear Mike

I've some question about banker's rounding. Why 0.535 goes to 0.54, 0.565 goes to 0.56 and 0.575 goes to 0.57 and 0.585 goes to 0.58? I'm having problem explain to my boss
Please help

Best regards
Fion H


----- Mike Sherrill wrote: ----

On Wed, 10 Dec 2003 09:16:56 -0800, Tim Ferguso

[snip

The bankers' rule says that all ties of this nature fall
toward an even number, so 2.5 -> 2 but 3.5 -> 4. On average, this is said
to be fairer in some sense, although I confess I cannot see it.

[snip

"Drift" describes what happens when you repeatedly add and subtrac
numbers that are close together. Here's what happens if you star
with 1, and repeatedly subtract and add 0.455, arithmetically roundin
(elementary school rounding) each result to two places

1.00 + 0.455 = 1.455, rounds to 1.4
1.46 - 0.455 = 1.005, rounds to 1.0
1.01 + 0.455 = 1.465, rounds to 1.4
1.47 - 0.455 = 1.015, rounds to 1.0

The result drifts away from the original value. Here's what happen
if you do the same thing, rounding instead to the nearest even numbe
("banker's rounding")

1.00 + 0.455 = 1.455, rounds to 1.4
1.46 - 0.455 = 1.005, rounds to 1.00
1.00 + 0.455 = 1.455, rounds to 1.4
1.46 - 0.455 = 1.005, rounds to 1.00

 

[Mike Sherrill]

Why 0.535 goes to 0.54, 0.565 goes to 0.56 and 0.575 goes to 0.57 and 0.585 goes to 0.58?

When you do this in the debug window

? Round(0.575, 2)
0.57

Access interprets 0.575 as a Double.

? TypeName(0.575)
Double

But you don't necessarily *get* 0.575. You get the closest
approximation to it within the domain of double-precision
floating-point numbers. That value is actually very slightly less
than 0.575.

Use Currency (for example) to see the expected behavior.

? Round(CCur(0.575), 2)
0.58

If you really want to round the way I learned in elementary school,
try Format().

? Format(0.565, "0.00")
0.57

And you should probably wrap Format() in the appropriate conversion
function.

 

[Tim Ferguson]

Here's what happens if you start
with 1, and repeatedly subtract and add 0.455, arithmetically rounding
(elementary school rounding) each result to two places.

This of course is a profoundly nonsensical thing to say. If you are working
to 2 dp, then there is no such value as 0.455; or if you are calculating to
three dp then arbitrarily carrying out some (any) function of the final
digit is going to cause problems. It would be no less correct to round up
if there is a letter "E" in the final digit. Let's hear it for Ravers'
Rounding?

If only they still taught maths in schools .... <sniff>


B Wishes


Tim F

 

[Mike Sherrill]

This of course is a profoundly nonsensical thing to say. If you are working
to 2 dp, then there is no such value as 0.455;

[snip]

? 1.25 * 1.25
1.5625