Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
If in a cell on a spreadsheet, I enter the following equation:
=ROUND(50.5, 0) I obtain the result 51, which I would expect, since 0.5 should be rounded up. If I write the following VB funcion: Function RoundFunc(Mark As Integer) As Integer RoundFunc = Round(Mark, 0) End Function And then enter the following equation into a cell: =RoundFunc(50.5) I obtain the result 50. i.e. 0.5 is rounded down Why the difference? Is there a function that I can use in my VB code that will round 0.5 up rather than down? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
"Andrew" wrote in message ... If in a cell on a spreadsheet, I enter the following equation: =ROUND(50.5, 0) I obtain the result 51, which I would expect, since 0.5 should be rounded up. If I write the following VB funcion: Function RoundFunc(Mark As Integer) As Integer RoundFunc = Round(Mark, 0) End Function And then enter the following equation into a cell: =RoundFunc(50.5) I obtain the result 50. i.e. 0.5 is rounded down Why the difference? Is there a function that I can use in my VB code that will round 0.5 up rather than down? When u use an integer as parameter for your function, you will loose all numbers after the decimal point! The solution is to use a double for the parameter. Glenn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
Andrew,
There is no VBA 'Round' function (not w/ Excel 97 anyway). You could use cLng instead: When the fractional part is exactly 0.5, CInt and CLng always round it to the nearest even number. For example, 0.5 rounds to 0, and 1.5 rounds to 2. CInt and CLng differ from the Fix and Int functions, which truncate, rather than round, the fractional part of a number. Also, Fix and Int always return a value of the same type as is passed in. This is still a bit querky on the 0.5 fraction. To always round up from 0.5 try: MyRound = int(MyConst + 0.5) David -----Original Message----- If in a cell on a spreadsheet, I enter the following equation: =ROUND(50.5, 0) I obtain the result 51, which I would expect, since 0.5 should be rounded up. If I write the following VB funcion: Function RoundFunc(Mark As Integer) As Integer RoundFunc = Round(Mark, 0) End Function And then enter the following equation into a cell: =RoundFunc(50.5) I obtain the result 50. i.e. 0.5 is rounded down Why the difference? Is there a function that I can use in my VB code that will round 0.5 up rather than down? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
The problem is not with VBA, you need to read the number
in as a decimal number not an integer: Function RoundFunc(Mark As Double) As Integer RoundFunc = Round(Mark, 0) End Function -----Original Message----- If in a cell on a spreadsheet, I enter the following equation: =ROUND(50.5, 0) I obtain the result 51, which I would expect, since 0.5 should be rounded up. If I write the following VB funcion: Function RoundFunc(Mark As Integer) As Integer RoundFunc = Round(Mark, 0) End Function And then enter the following equation into a cell: =RoundFunc(50.5) I obtain the result 50. i.e. 0.5 is rounded down Why the difference? Is there a function that I can use in my VB code that will round 0.5 up rather than down? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
Andrew,
Perhaps the standard for rounding is different in different parts of the world? Back in school (here in Sweden) I was tought to round to the mearest EVEN number when the value is just in-between. 49,5 should be rounded to 50 50,5 should be rounded to 50 51,5 should be rounded to 52 52,5 should be rounded to 52 53,5 should be rounded to 54 ....and so on. VBA's ROUND function behaves this way, just the way I want it to. VBA also works like this when automatically converting from floting-point to integer data types. See this example: Sub Test() Dim d As Double Dim i As Integer MsgBox Round(3.5,0) '4 MsgBox Round(234.5,0) '234 d = 49.5 i = d MsgBox i '50 d = 52.5 '52 i = d MsgBox i End Sub Excel's ROUND worksheet function always rounds UP (or actually away from zero). You can use Excel's rounding function in VBA, like this: RoundFunc = Application.Round(Mark, 0) Also, as Glenn told you, you should change the datatype of the Mark parameter to Double. -- Ture Magnusson Microsoft MVP - Excel Karlstad, Sweden "Andrew" wrote in message ... If in a cell on a spreadsheet, I enter the following equation: =ROUND(50.5, 0) I obtain the result 51, which I would expect, since 0.5 should be rounded up. If I write the following VB funcion: Function RoundFunc(Mark As Integer) As Integer RoundFunc = Round(Mark, 0) End Function And then enter the following equation into a cell: =RoundFunc(50.5) I obtain the result 50. i.e. 0.5 is rounded down Why the difference? Is there a function that I can use in my VB code that will round 0.5 up rather than down? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
When u use an integer as parameter for your function, you will loose all numbers after the decimal point! The solution is to use a double for the parameter. Glenn A typo. I tried numerous different versions of the function, and posted the wrong one! The problem still exists with: Function RoundFuncDouble(Mark As Double) As Double RoundFuncDouble = Round(Mark, 0) End Function A cell containing =RoundFuncDouble(50.5) still returns 50 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
James wrote:
The problem is not with VBA, you need to read the number in as a decimal number not an integer: Function RoundFunc(Mark As Double) As Integer RoundFunc = Round(Mark, 0) End Function Sorry, a typo. I tried numerous different versions of the function, and posted the wrong one! The problem still exists with the above A cell containing =RoundFunc(50.5) still returns 50 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
Ture Magnusson wrote:
Andrew, Perhaps the standard for rounding is different in different parts of the world? Back in school (here in Sweden) I was tought to round to the mearest EVEN number when the value is just in-between. 49,5 should be rounded to 50 50,5 should be rounded to 50 51,5 should be rounded to 52 52,5 should be rounded to 52 53,5 should be rounded to 54 ...and so on. VBA's ROUND function behaves this way, just the way I want it to. VBA also works like this when automatically converting from floting-point to integer data types. See this example: Sub Test() Dim d As Double Dim i As Integer MsgBox Round(3.5,0) '4 MsgBox Round(234.5,0) '234 d = 49.5 i = d MsgBox i '50 d = 52.5 '52 i = d MsgBox i End Sub Excel's ROUND worksheet function always rounds UP (or actually away from zero). You can use Excel's rounding function in VBA, like this: RoundFunc = Application.Round(Mark, 0) Also, as Glenn told you, you should change the datatype of the Mark parameter to Double. Ah!! This explains the problem. Many thanks. I am aware of the alternative method of rounding (to the nearest even number), although I'm not clear as to the reason for this. Nevertheless, my application specifically requires the same functionality as the ROUND worksheet function, so your above suggestion seems to be my solution. What I find suprising is that the two functions behave differently, and that Microsoft appear to make no mention of the difference in their help files. Rather inconsistent and very confusing. (P.S. My use of Integer parameter in the original posting was a typo. I tried many solutions and copied the wrong one). |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
Rounding away 5 to produce an even number is the ASTM standard for
rounding. MS calls it "Bankers' rounding", though I have yet to see any evidence that it is used in banking. I have also seen it called "unbiased rounding" since it tends to equalize the number times you round up vs. round down. It also corresponds to the IEEE standard for rounding. Excel 2000 introduced a Round function, and it rounds in this fashion. Rounding away 5 to produce the next larger number is a simplified version of the above rule, that most of us in the US were taught in elementary school. It is a poor way to treat data, since it tends to introduce rounding biases, but is specified by the USP (United States Pharmacopoeia), the IRS (U.S. Internal Revenue Service), and by European banking web sites for Euro conversions. This is the way that Excel's worksheet ROUND function has always rounded. Jerry Ture Magnusson wrote: Andrew, Perhaps the standard for rounding is different in different parts of the world? Back in school (here in Sweden) I was tought to round to the mearest EVEN number when the value is just in-between. 49,5 should be rounded to 50 50,5 should be rounded to 50 51,5 should be rounded to 52 52,5 should be rounded to 52 53,5 should be rounded to 54 ...and so on. VBA's ROUND function behaves this way, just the way I want it to. VBA also works like this when automatically converting from floting-point to integer data types. See this example: Sub Test() Dim d As Double Dim i As Integer MsgBox Round(3.5,0) '4 MsgBox Round(234.5,0) '234 d = 49.5 i = d MsgBox i '50 d = 52.5 '52 i = d MsgBox i End Sub Excel's ROUND worksheet function always rounds UP (or actually away from zero). You can use Excel's rounding function in VBA, like this: RoundFunc = Application.Round(Mark, 0) Also, as Glenn told you, you should change the datatype of the Mark parameter to Double. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
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;Q196652 HOWTO: Implement Custom Rounding Procedures http://support.microsoft.com/default...;en-us;Q225330 OFF2000: New Round Function in Visual Basic for Applications 6.0 http://support.microsoft.com/default...;en-us;Q209996 ACC2000: How to Round a Number Up or Down by a Desired Increment http://support.microsoft.com/default...;en-us;Q279755 INFO: Visual Basic and Arithmetic Precision -- Regards, Tom Ogilvy "Andrew" wrote in message ... Ture Magnusson wrote: Andrew, Perhaps the standard for rounding is different in different parts of the world? Back in school (here in Sweden) I was tought to round to the mearest EVEN number when the value is just in-between. 49,5 should be rounded to 50 50,5 should be rounded to 50 51,5 should be rounded to 52 52,5 should be rounded to 52 53,5 should be rounded to 54 ...and so on. VBA's ROUND function behaves this way, just the way I want it to. VBA also works like this when automatically converting from floting-point to integer data types. See this example: Sub Test() Dim d As Double Dim i As Integer MsgBox Round(3.5,0) '4 MsgBox Round(234.5,0) '234 d = 49.5 i = d MsgBox i '50 d = 52.5 '52 i = d MsgBox i End Sub Excel's ROUND worksheet function always rounds UP (or actually away from zero). You can use Excel's rounding function in VBA, like this: RoundFunc = Application.Round(Mark, 0) Also, as Glenn told you, you should change the datatype of the Mark parameter to Double. Ah!! This explains the problem. Many thanks. I am aware of the alternative method of rounding (to the nearest even number), although I'm not clear as to the reason for this. Nevertheless, my application specifically requires the same functionality as the ROUND worksheet function, so your above suggestion seems to be my solution. What I find suprising is that the two functions behave differently, and that Microsoft appear to make no mention of the difference in their help files. Rather inconsistent and very confusing. (P.S. My use of Integer parameter in the original posting was a typo. I tried many solutions and copied the wrong one). |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Round( ) function
On Wed, 12 Nov 2003 12:22:52 -0000, "Andrew" wrote:
If in a cell on a spreadsheet, I enter the following equation: =ROUND(50.5, 0) I obtain the result 51, which I would expect, since 0.5 should be rounded up. If I write the following VB funcion: Function RoundFunc(Mark As Integer) As Integer RoundFunc = Round(Mark, 0) End Function And then enter the following equation into a cell: =RoundFunc(50.5) I obtain the result 50. i.e. 0.5 is rounded down Why the difference? Is there a function that I can use in my VB code that will round 0.5 up rather than down? The difference is because the VBA Round function uses a different algorithms, and 0.5 will be rounded up or down depending on whether the preceding number is odd or even. The equivalent VBA function, to round like the worksheet: Application.Worksheetfunction.Round(n,digits) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round Function | Excel Worksheet Functions | |||
Round function | Excel Discussion (Misc queries) | |||
how do i set up round function | Excel Worksheet Functions | |||
help with round function | Excel Worksheet Functions | |||
Round Function | Excel Discussion (Misc queries) |