Rounding Error

M

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
 
D

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
= = = = = = = = = = = = = = = = =


Matthew W said:
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?
 
T

Tom Ogilvy

A google search:

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

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

y

Jerry said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rounding 2
ROUNDING 2
Rounding problem 9
Bug with the function Round ! 8
Problem using & 4
Populate User Form Text Box from Worksheet? 1
Use a Variable in a formula 4
Rounding in Excel 2

Top