VB Function Round vs Excel function Round not behaving the same Od

Here how it goes :

Have a VB function :

Public Function MyRound(Data)
MyRound = Round(Data, 1)
End Function

Have a excel sheet with cells on one side with the "MyRound" function and on
the other side the Round function from Excel, guess what they don't give the
same results.

Here the example :
Data Fonction Round Round Excel
=Myround(A1) =Round(A1;1)
0,15 0,2 0,2 Ok
0,25 0,2 0,3 Wrong from VB
0,35 0,4 0,4 Ok
0,45 0,4 0,5 Wrong again From VB

My understanding of the error is : don't have an odd number with an even
one, VB will not round your numbers ????

I've tried this with Excel 2003 and 2000, same results.

I'm asking Microsoft to post a official answer to this and also posting a
bug report for it, and to give a fix.

Thanks and lookout for those even numbers !! Don't you find this Odd ??

--
Bud

Use this:

Public Function MyRound(Data)
MyRound = application.Round(Data, 1)
End Function



Bud wrote:
> Here how it goes :
>
> Have a VB function :
>
> Public Function MyRound(Data)
> MyRound = Round(Data, 1)
> End Function
>
> Have a excel sheet with cells on one side with the "MyRound" function and on
> the other side the Round function from Excel, guess what they don't give the
> same results.
>
> Here the example :
> Data Fonction Round Round Excel
> =Myround(A1) =Round(A1;1)
> 0,15 0,2 0,2 Ok
> 0,25 0,2 0,3 Wrong from VB
> 0,35 0,4 0,4 Ok
> 0,45 0,4 0,5 Wrong again From VB
>
> My understanding of the error is : don't have an odd number with an even
> one, VB will not round your numbers ????
>
> I've tried this with Excel 2003 and 2000, same results.
>
> I'm asking Microsoft to post a official answer to this and also posting a
> bug report for it, and to give a fix.
>
> Thanks and lookout for those even numbers !! Don't you find this Odd ??
>
> --
> Bud

Hey bud.

VBA is a separate application from Excel. Excel just uses VBA as its macro
language, same as Word, Powerpoint, Autocad, Corel Draw and others.

The round function was introduced in VBA in version 6 - first used in
xl2000.

This was addressed back then and is by design. There is no reason to expect
that the VBA round function would round the same as Excel. Different
products, different rules.

http://support.microsoft.com/default...;en-us;Q194983
PRB: Round Function different in VBA 6 and Excel Spreadsheet

http://support.microsoft.com/default...;en-us;Q225330
OFF2000: New Round Function in Visual Basic for Applications 6.0

--
Regards,
Tom Ogilvy



"Bud" <[email protected]> wrote in message
news:[email protected]...
> Here how it goes :
>
> Have a VB function :
>
> Public Function MyRound(Data)
> MyRound = Round(Data, 1)
> End Function
>
> Have a excel sheet with cells on one side with the "MyRound" function and
> on
> the other side the Round function from Excel, guess what they don't give
> the
> same results.
>
> Here the example :
> Data Fonction Round Round Excel
> =Myround(A1) =Round(A1;1)
> 0,15 0,2 0,2 Ok
> 0,25 0,2 0,3 Wrong from VB
> 0,35 0,4 0,4 Ok
> 0,45 0,4 0,5 Wrong again From VB
>
> My understanding of the error is : don't have an odd number with an even
> one, VB will not round your numbers ????
>
> I've tried this with Excel 2003 and 2000, same results.
>
> I'm asking Microsoft to post a official answer to this and also posting a
> bug report for it, and to give a fix.
>
> Thanks and lookout for those even numbers !! Don't you find this Odd ??
>
> --
> Bud

Thank you for the reply, very usefull !!

It would have been nice from microsoft to include such information in the
help of VBA in office 2003, since it has been know for a while...

The help is not explicite on how it is rounding numbers.
(banker's rounding, Its funny)
--
Bud


"Tom Ogilvy" wrote:

> Hey bud.
>
> VBA is a separate application from Excel. Excel just uses VBA as its macro
> language, same as Word, Powerpoint, Autocad, Corel Draw and others.
>
> The round function was introduced in VBA in version 6 - first used in
> xl2000.
>
> This was addressed back then and is by design. There is no reason to expect
> that the VBA round function would round the same as Excel. Different
> products, different rules.
>
> http://support.microsoft.com/default...;en-us;Q194983
> PRB: Round Function different in VBA 6 and Excel Spreadsheet
>
> http://support.microsoft.com/default...;en-us;Q225330
> OFF2000: New Round Function in Visual Basic for Applications 6.0
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Bud" <[email protected]> wrote in message
> news:[email protected]...
> > Here how it goes :
> >
> > Have a VB function :
> >
> > Public Function MyRound(Data)
> > MyRound = Round(Data, 1)
> > End Function
> >
> > Have a excel sheet with cells on one side with the "MyRound" function and
> > on
> > the other side the Round function from Excel, guess what they don't give
> > the
> > same results.
> >
> > Here the example :
> > Data Fonction Round Round Excel
> > =Myround(A1) =Round(A1;1)
> > 0,15 0,2 0,2 Ok
> > 0,25 0,2 0,3 Wrong from VB
> > 0,35 0,4 0,4 Ok
> > 0,45 0,4 0,5 Wrong again From VB
> >
> > My understanding of the error is : don't have an odd number with an even
> > one, VB will not round your numbers ????
> >
> > I've tried this with Excel 2003 and 2000, same results.
> >
> > I'm asking Microsoft to post a official answer to this and also posting a
> > bug report for it, and to give a fix.
> >
> > Thanks and lookout for those even numbers !! Don't you find this Odd ??
> >
> > --
> > Bud
>
>
>

What MS calls "banker's rounding" has been considered best practice for at
least a century, and is specified by ASTM, IEEE (in its binary equivalent)
and many other standards bodies.

It is sometimes called "unbiased rounding" because it rounds to the nearest
rounded number with ties going up or down to make an even rounded number,
thus avoiding the small bias introduced by always rounding 5's up.

What does strike me as funny is how it ever came to be called "banker's
rounding" since as far as I can tell, banking and finance is about the only
area where it seems to never be used.

Jerry

"Bud" wrote:

> Thank you for the reply, very usefull !!
>
> It would have been nice from microsoft to include such information in the
> help of VBA in office 2003, since it has been know for a while...
>
> The help is not explicite on how it is rounding numbers.
> (banker's rounding, Its funny)
> --
> Bud

May be it's a case of the banking sector saying that they utilise this
unbiased rounding when in practice they didn't. Then pocketed the
difference.

NickHK

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:[email protected]...
> What MS calls "banker's rounding" has been considered best practice for at
> least a century, and is specified by ASTM, IEEE (in its binary equivalent)
> and many other standards bodies.
>
> It is sometimes called "unbiased rounding" because it rounds to the
nearest
> rounded number with ties going up or down to make an even rounded number,
> thus avoiding the small bias introduced by always rounding 5's up.
>
> What does strike me as funny is how it ever came to be called "banker's
> rounding" since as far as I can tell, banking and finance is about the
only
> area where it seems to never be used.
>
> Jerry
>
> "Bud" wrote:
>
> > Thank you for the reply, very usefull !!
> >
> > It would have been nice from microsoft to include such information in
the
> > help of VBA in office 2003, since it has been know for a while...
> >
> > The help is not explicite on how it is rounding numbers.
> > (banker's rounding, Its funny)
> > --
> > Bud