Round doesnt work the way it is expected

[Guest]

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

 

[Don Guillett]

Interesting. My test did the same. Try

application.round(x*i,0)

 

[Nick Hodge]

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
(e-mail address removed)
www.nickhodge.co.uk

 

[Ron Rosenfeld]

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/archive/2003/09/26/53107.aspx for further
discussion.


--ron

 

[Nick Hodge]

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
(e-mail address removed)
www.nickhodge.co.uk

 

[Don Guillett]

Then why did 1.5 and 4.5 not round the same?

 

[Don Guillett]

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

 

[Bernard Liengme]

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.

 

[Ron Rosenfeld]

Then why did 1.5 and 4.5 not round the same?

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

 

[Harlan Grove]

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.