Round doesnt work the way it is expected
 

Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round(" & x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

Round doesnt work the way it is expected
 

Interesting. My test did the same. Try

application.round(x*i,0)

--
Don Guillett
SalesAid Software

"Carlo" wrote in message
...
Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round(" &
x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

Round doesnt work the way it is expected
 

Hi Don,

yeah, application.round works fine.

Still strange though.....

thanks a lot

Carlo

"Don Guillett" wrote:

Interesting. My test did the same. Try

application.round(x*i,0)

--
Don Guillett
SalesAid Software

"Carlo" wrote in message
...
Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round(" &
x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

Round doesnt work the way it is expected
 

Carlo

I suspect it is something to do with VBAs handling of the IEEE spec for
floating point arithmetic on binary machines...it is strange though and what
is even more frustrating this round works, as you have seen, different to
Excel's ROUND, different to Access's Round function, which uses bankers
rounding!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Carlo" wrote in message
...
Hi Don,

yeah, application.round works fine.

Still strange though.....

thanks a lot

Carlo

"Don Guillett" wrote:

Interesting. My test did the same. Try

application.round(x*i,0)

--
Don Guillett
SalesAid Software

"Carlo" wrote in message
...
Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round("
&
x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

Round doesnt work the way it is expected
 

On Wed, 20 Dec 2006 05:25:00 -0800, Carlo
wrote:

Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round(" & x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

The VBA Round function uses what has been termed, for some unknown reason,
"banker's rounding". When a value is "on the margin -- e.g. n.5), the value
will be rounded to the nearest even number. See
http://blogs.msdn.com/ericlippert/ar.../26/53107.aspx for further
discussion.


--ron

Round doesnt work the way it is expected
 

Ah, thanks for that Ron. It must be the same as Access's then. The rumour
is it always falls on the side of the banker!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Ron Rosenfeld" wrote in message
...
On Wed, 20 Dec 2006 05:25:00 -0800, Carlo

wrote:

Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round(" &
x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

The VBA Round function uses what has been termed, for some unknown reason,
"banker's rounding". When a value is "on the margin -- e.g. n.5), the
value
will be rounded to the nearest even number. See
http://blogs.msdn.com/ericlippert/ar.../26/53107.aspx for
further
discussion.


--ron

Round doesnt work the way it is expected
 

Then why did 1.5 and 4.5 not round the same?

--
Don Guillett
SalesAid Software

"Ron Rosenfeld" wrote in message
...
On Wed, 20 Dec 2006 05:25:00 -0800, Carlo

wrote:

Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round(" &
x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

The VBA Round function uses what has been termed, for some unknown reason,
"banker's rounding". When a value is "on the margin -- e.g. n.5), the
value
will be rounded to the nearest even number. See
http://blogs.msdn.com/ericlippert/ar.../26/53107.aspx for
further
discussion.


--ron

Round doesnt work the way it is expected
 

The VBA Round() function uses what MS calls "Banker's rounding"
http://support.microsoft.com/kb/194983
which rounds to the nearest rounded number, but handles ties differently
than you were expecting. You expected ties to always round up, which can
introduce a rounding bias. The so called "Banker's rounding" tries to
roughly balance the number of times that ties round up or down by rounding
ties so that the final rounded digit is even. This has been the ASTM
standard for rounding since the early 1940's and has been best practice for
nearly a century.

Nick is partly right, in that the IEEE 754 standard specifies that rounding
of internal binary numbers be to an even final digit. IEEE 854 extends this
to natively decimal calculations, but I don't think that it applies to how
you round the decimal representation of natively binary calculations. About
10 years ago, an informal survey of computer languages found them about
evenly divided as to which rounding method they implemented.

Why MS calls it "Banker's rounding" is a mystery to me, since as far as I
can determine, bankers have never used it. I would welcome an explanation of
the history of this name, evidence that bankers have ever used it, or
pre-1940 references to this type of rounding.

Jerry

"Carlo" wrote:

Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round(" & x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

Round doesnt work the way it is expected
 

Exact ties are rounded to have an even final rounded digit: that would be up
for 1.5 and down for 4.5.

Jerry

"Don Guillett" wrote:

Then why did 1.5 and 4.5 not round the same?

--
Don Guillett
SalesAid Software

"Ron Rosenfeld" wrote in message
...
On Wed, 20 Dec 2006 05:25:00 -0800, Carlo

wrote:

Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round(" &
x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

The VBA Round function uses what has been termed, for some unknown reason,
"banker's rounding". When a value is "on the margin -- e.g. n.5), the
value
will be rounded to the nearest even number. See
http://blogs.msdn.com/ericlippert/ar.../26/53107.aspx for
further
discussion.


--ron

Round doesnt work the way it is expected
 

So, then multiply by i+.0000000000000001
I didn't count the 0's
round(x*i+.0000000000001)
--
Don Guillett
SalesAid Software

"Jerry W. Lewis" wrote in message
...
The VBA Round() function uses what MS calls "Banker's rounding"
http://support.microsoft.com/kb/194983
which rounds to the nearest rounded number, but handles ties differently
than you were expecting. You expected ties to always round up, which can
introduce a rounding bias. The so called "Banker's rounding" tries to
roughly balance the number of times that ties round up or down by rounding
ties so that the final rounded digit is even. This has been the ASTM
standard for rounding since the early 1940's and has been best practice
for
nearly a century.

Nick is partly right, in that the IEEE 754 standard specifies that
rounding
of internal binary numbers be to an even final digit. IEEE 854 extends
this
to natively decimal calculations, but I don't think that it applies to how
you round the decimal representation of natively binary calculations.
About
10 years ago, an informal survey of computer languages found them about
evenly divided as to which rounding method they implemented.

Why MS calls it "Banker's rounding" is a mystery to me, since as far as I
can determine, bankers have never used it. I would welcome an explanation
of
the history of this name, evidence that bankers have ever used it, or
pre-1940 references to this type of rounding.

Jerry

"Carlo" wrote:

Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round("
& x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

Round doesnt work the way it is expected
 

I would have liked to have heard from Australian readers since I understand
that "banker's rounding" is the only legal form there.
In Nova Scotia (Canada), this rounding of last 5 to an even value was part
of the so-called new math at one time but seems to have been dropped.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jerry W. Lewis" wrote in message
...
The VBA Round() function uses what MS calls "Banker's rounding"
http://support.microsoft.com/kb/194983
which rounds to the nearest rounded number, but handles ties differently
than you were expecting. You expected ties to always round up, which can
introduce a rounding bias. The so called "Banker's rounding" tries to
roughly balance the number of times that ties round up or down by rounding
ties so that the final rounded digit is even. This has been the ASTM
standard for rounding since the early 1940's and has been best practice
for
nearly a century.

Nick is partly right, in that the IEEE 754 standard specifies that
rounding
of internal binary numbers be to an even final digit. IEEE 854 extends
this
to natively decimal calculations, but I don't think that it applies to how
you round the decimal representation of natively binary calculations.
About
10 years ago, an informal survey of computer languages found them about
evenly divided as to which rounding method they implemented.

Why MS calls it "Banker's rounding" is a mystery to me, since as far as I
can determine, bankers have never used it. I would welcome an explanation
of
the history of this name, evidence that bankers have ever used it, or
pre-1940 references to this type of rounding.

Jerry

"Carlo" wrote:

Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round("
& x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

Round doesnt work the way it is expected
 

On Wed, 20 Dec 2006 08:50:41 -0600, "Don Guillett"
wrote:

Then why did 1.5 and 4.5 not round the same?

--
Don Guillett
SalesAid Software

Huh? Using the VBA Round function, they both round to the nearest even digit.

What do you mean by "not round the same"?

1.5 is halfway between 1 and 2; 2 is the nearest even number

4.5 is halfway between 4 and 5; 4 is the nearest even number




--ron

Round doesnt work the way it is expected
 

Currently my only evidence on international banking is that conversions to or
from Euros round 5's up, but I would be very interested in learning if
rounding ties to even final digits is used somewhere in banking.

The method (rounding ties to even final digits) is called "the computer's
rule" ("computer" being a person who computes, not a modern electronic
device) in the 1906 edition of Robert Woodward's book "Probability and Theory
of Errors".

I have an unconfirmed anecdote that the method was advocated by AMS ca. 1910
and thus became known (at Bell Labs) as "AMS rounding."

ASTM adopted it as a standard in 1940, and it has remained the ASTM standard
for rounding to this day. I do not know when it was adopted as SI and ISO
standards, but I understand that it was already in place with those bodies by
1970.

Churchill Eisenhart's 1947 paper "Effects of Rounding or Grouping Data"
stated without reference that the method was "well established".

Jerry

"Bernard Liengme" wrote:

I would have liked to have heard from Australian readers since I understand
that "banker's rounding" is the only legal form there.
In Nova Scotia (Canada), this rounding of last 5 to an even value was part
of the so-called new math at one time but seems to have been dropped.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jerry W. Lewis" wrote in message
...
The VBA Round() function uses what MS calls "Banker's rounding"
http://support.microsoft.com/kb/194983
which rounds to the nearest rounded number, but handles ties differently
than you were expecting. You expected ties to always round up, which can
introduce a rounding bias. The so called "Banker's rounding" tries to
roughly balance the number of times that ties round up or down by rounding
ties so that the final rounded digit is even. This has been the ASTM
standard for rounding since the early 1940's and has been best practice
for
nearly a century.

Nick is partly right, in that the IEEE 754 standard specifies that
rounding
of internal binary numbers be to an even final digit. IEEE 854 extends
this
to natively decimal calculations, but I don't think that it applies to how
you round the decimal representation of natively binary calculations.
About
10 years ago, an informal survey of computer languages found them about
evenly divided as to which rounding method they implemented.

Why MS calls it "Banker's rounding" is a mystery to me, since as far as I
can determine, bankers have never used it. I would welcome an explanation
of
the history of this name, evidence that bankers have ever used it, or
pre-1940 references to this type of rounding.

Jerry

"Carlo" wrote:

Hello all

i'm a little confused here. Hope somebody can help me.
With following code:
---------------------------------------------------------
Sub Whyyyyyyyyyyyyyyyyyy()
x = 1.5
For i = 1 To 4
Debug.Print "(" & x & " * " & i & ") = " & x * i & " == Round("
& x
* i & ") = " & Round(x * i, 0)
Next i
End Sub
---------------------------------------------------------
VBA returns me:
------------------------
(1.5 * 1) = 1.5 == Round(1.5) = 2
(1.5 * 2) = 3 == Round(3) = 3
(1.5 * 3) = 4.5 == Round(4.5) = 4
(1.5 * 4) = 6 == Round(6) = 6
------------------------

Why is Round(1.5) = 2 and Round(4.5) = 4 ?????

I don't really get it!


Thanks for any help

Carlo

Round doesnt work the way it is expected
 

Jerry W. Lewis wrote...
....
. . . About
10 years ago, an informal survey of computer languages found them about
evenly divided as to which rounding method they implemented.
....

Which computer languages? C, C++, APL, APL2, Perl and most other
scripting languages don't have any built-in rounding functions. FORTRAN
has both AINT and ANINT, the former always truncating, the latter
always the opposite. As far as I can tell, Java and VB may be the only
widely used languages that include single rounding functions.