Round doesnt work the way it is expected

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
Click to expand...

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
 
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
 
So, then multiply by i+.0000000000000001
I didn't count the 0's
round(x*i+.0000000000001)
 
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.
 
Then why did 1.5 and 4.5 not round the same?
Click to expand...

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
 
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.
Click to expand...
....

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.
 
Back
Top