Rounding Error

[Matthew W]

Excel has a rounding error. When rounding 2.5 it returns 3. However, the basic rules of rounding states that when the digit following the number to be rounded is 5 you always round to an even number. In other Microsoft Tools this does not occur. For example, if you perform the following two computations in excel and Visual Basic, Visual Basic will give you the correct answer. Is there another function that can be used to resolve this problem

The correct answer
4=Round(2+.5, 0) * 2

The incorrect answer
6=Round(2+.5, 0) * 2

 

[Dana DeLouis]

PRB: Round Function different in VBA 6 and Excel Spreadsheet
http://support.microsoft.com/default.aspx?scid=kb;en-us;194983&Product=xlw


Sub Demo()
Debug.Print Round(2.5, 0)
Debug.Print WorksheetFunction.Round(2.5, 0)

'Banker Rounding Also
Debug.Print CLng(2.5)
Debug.Print CLng(3.5)
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Excel has a rounding error. When rounding 2.5 it returns 3. However, the

basic rules of rounding states that when the digit following the number to
be rounded is 5 you always round to an even number. In other Microsoft Tools
this does not occur. For example, if you perform the following two
computations in excel and Visual Basic, Visual Basic will give you the
correct answer. Is there another function that can be used to resolve this
problem?

 

[Matthew W]

Thank you very much, I will use vba for rounding.

 

[Tom Ogilvy]

A google search:

Your search - "basic rules of rounding" - did not match any documents.

 

[Jerry W. Lewis]

As noted under your re-post to
microsoft.public.excel.worksheet.functions, you can write a UDF to
access the VBA round from the worksheet

http://groups.google.com/groups?selm=3E55A396.8080708@no_e-mail.com

has relevant suggestions.

It is generally sufficient to post a question in only one excel group,
since most of us frequent all of the groups. If a post is really
relevant for multiple groups, please address only one query to all the
relevant groups, to avoid our duplicating each others efforts.

Jerry

 

[y]

It is generally sufficient to post a question in only one excel group,
since most of us frequent all of the groups. If a post is really
relevant for multiple groups, please address only one query to all the
relevant groups, to avoid our duplicating each others efforts.

Or putting an [X-post] or [Cross-post] in the subject.
Not all the people follow all the groups.
Alex.