Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding in VBA - Any ideas?
Hi all,
This rounding issue in VBA drive me crazy! Many posts report that VBA uses the bankers rounding. But I need the standard rounding. So I tried to make my own Mathematical Rounding function. For some mysterieous reasons it does not always work as expected. Anyone who likes solving strange behaviour of VBA is kindly invited to see why. MathRound(101.68000000000001,5) works OK -- 101.68000 MathRound(102.63000000000001,5) does not work --- 102.63001 (102.63000 expected) The function: Function MathRound(dblInput As Double, intDigits As Integer) As Double 'Rounds the input value (DblInput) to the number of digits specified in intDigits 'The container values dblContainer and dblContner seem to be necessary because 'without them the calculations are wrongly passed through. Dim dblContainer As Double Dim lngExpon As Long Dim lngLong As Long Dim dblContner As Double dblContainer = dblInput + 5 * (10 ^ -(intDigits + 1)) 'Add a bit to make sure truncation is done right. lngExpon = 10 ^ intDigits 'calculate the power of ten to be multiplied based on the number of digits dblContner = Round(dblContainer * lngExpon, 0) 'PUT value in second container lngLong = Fix(dblContner) 'truncate value MathRound = lngLong / lngExpon 'Devide back to the correct number of digits End Function Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding in VBA - Any ideas?
Excel's precision is only 15 decimal digits. You're using 17.
Some information he http://www.cpearson.com/Excel/rounding.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Michiel via OfficeKB.com" <u40062@uwe wrote in message news:89e4203ddd3d3@uwe... | Hi all, | | This rounding issue in VBA drive me crazy! | Many posts report that VBA uses the bankers rounding. But I need the standard | rounding. | | So I tried to make my own Mathematical Rounding function. For some | mysterieous reasons it does not always work as expected. | | Anyone who likes solving strange behaviour of VBA is kindly invited to see | why. | | MathRound(101.68000000000001,5) works OK -- 101.68000 | MathRound(102.63000000000001,5) does not work --- 102.63001 (102.63000 | expected) | | The function: | Function MathRound(dblInput As Double, intDigits As Integer) As Double | 'Rounds the input value (DblInput) to the number of digits specified in | intDigits | 'The container values dblContainer and dblContner seem to be necessary | because | 'without them the calculations are wrongly passed through. | Dim dblContainer As Double | Dim lngExpon As Long | Dim lngLong As Long | Dim dblContner As Double | dblContainer = dblInput + 5 * (10 ^ -(intDigits + 1)) 'Add a bit to make | sure truncation is done right. | lngExpon = 10 ^ intDigits 'calculate the power of ten to be multiplied | based on the number of digits | dblContner = Round(dblContainer * lngExpon, 0) 'PUT value in second | container | lngLong = Fix(dblContner) 'truncate value | MathRound = lngLong / lngExpon 'Devide back to the correct number of | digits | End Function | | Thanks! | | -- | Message posted via OfficeKB.com | http://www.officekb.com/Uwe/Forums.a...tions/200809/1 | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding in VBA - Any ideas?
On Mon, 08 Sep 2008 15:30:24 GMT, "Michiel via OfficeKB.com" <u40062@uwe
wrote: Hi all, This rounding issue in VBA drive me crazy! Many posts report that VBA uses the bankers rounding. But I need the standard rounding. So I tried to make my own Mathematical Rounding function. For some mysterieous reasons it does not always work as expected. Anyone who likes solving strange behaviour of VBA is kindly invited to see why. MathRound(101.68000000000001,5) works OK -- 101.68000 MathRound(102.63000000000001,5) does not work --- 102.63001 (102.63000 expected) To do rounding the same way that Excel does on the worksheet, use Application.worksheetfunction.Round(arg1, arg2) Note that the values you are using are specified with more precision than is available to Excel. If you want to use high precision numbers in your routine, you could input the values as a string, and then use the Decimal data type, which allows about 28 digit precision; or you could download and install the Xnumbers.xla add-in which allows up to 250 sigificant digits, and has an xround function which will operate on long numbers. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding in VBA - Any ideas?
Thank you both!
I do not wantt o use the worksheet function since I want the functio to be universal, so I can use it un VBA word and in Access as well. The idea of trunc-ing it via a string is an interesting one however! I'll start playing around with that one. THANKS! -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding in VBA - Any ideas?
MS chose not to display more than 15 digits because digits beyond the 15th
may not be what you expect (because numbers are stored in binary, not decimal). Consider dblContainer*lngExpon which you round to produce dblContner. To 17 figures, the actual values of dblContainer*lngExpon for your two cases are 10168000.500000000 10163000.500000002 the 1st number ends in exactly 0.5, so it rounds to the nearest even integer (down). The 2nd number exceeds 0.5, so it rounds up. VBA did what you told it to do, but that is different than what you wanted it to do. If I were you, I would look at CDbl(CStr(dblInput*lngExpon)). The CStr step removes figures beyond the 15th, so that fractional parts should round up or down depending on whether they are or < 0.5. Fractional values of exactly 0.5 can round as you choose. You said that you wanted 5's up rounding, but this approach will also give you more expected results in "banker's rounding" than the VBA Round function, which does not buffer against the vagaries of binary representations. Jerry "Michiel via OfficeKB.com" wrote: Hi all, This rounding issue in VBA drive me crazy! Many posts report that VBA uses the bankers rounding. But I need the standard rounding. So I tried to make my own Mathematical Rounding function. For some mysterieous reasons it does not always work as expected. Anyone who likes solving strange behaviour of VBA is kindly invited to see why. MathRound(101.68000000000001,5) works OK -- 101.68000 MathRound(102.63000000000001,5) does not work --- 102.63001 (102.63000 expected) The function: Function MathRound(dblInput As Double, intDigits As Integer) As Double 'Rounds the input value (DblInput) to the number of digits specified in intDigits 'The container values dblContainer and dblContner seem to be necessary because 'without them the calculations are wrongly passed through. Dim dblContainer As Double Dim lngExpon As Long Dim lngLong As Long Dim dblContner As Double dblContainer = dblInput + 5 * (10 ^ -(intDigits + 1)) 'Add a bit to make sure truncation is done right. lngExpon = 10 ^ intDigits 'calculate the power of ten to be multiplied based on the number of digits dblContner = Round(dblContainer * lngExpon, 0) 'PUT value in second container lngLong = Fix(dblContner) 'truncate value MathRound = lngLong / lngExpon 'Devide back to the correct number of digits End Function Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding in VBA - Any ideas?
Thanks again all for your help.
I used the approach with the string and created this function. I think it works perfactly. IF someone thinks it does not then please tell me! Function MathRound(dblInput As Double, Optional intDigits As Integer = 0) As Double 'This function performs a mathematical rounding. Of the dblInput value. 'It will not round like the round function of VB. 'IF specified it will round towards the intDigits digits. 'EXample: MathRound(1.5) - 2 And MathRound(2.5) - 3 Dim strTemp As String Dim dblTemp As Double Dim lngExpon As Long Dim lngPos As Long Dim n As Long lngExpon = 10 ^ intDigits 'The factor to multiply with in case more than 0 digits dblTemp = (dblInput * lngExpon) + 0.5 'Correction factor to truncate the number correctly strTemp = CStr(dblTemp) 'Hold the value in string to truncate it lngPos = InStr(1, strTemp, ".") 'find digit in string If lngPos 0 Then strTemp = Left(strTemp, lngPos - 1) 'Truncate dblTemp = CDbl(strTemp) 'Return number back to double MathRound = dblTemp / lngExpon 'Calculate back to required number of digits. End Function -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart Ideas??? | Excel Discussion (Misc queries) | |||
Help with a Task...any ideas? | Excel Discussion (Misc queries) | |||
Anyone else have any ideas?? | Excel Discussion (Misc queries) | |||
Any Ideas? | Excel Worksheet Functions | |||
Any Ideas | Excel Discussion (Misc queries) |