# Custom RoundUp function Error

#### Juggler_IN

##### Board Regular
The following RoundtoUp code is failing in few specific cases as compared with Excel RoundUp:

Code:
``````Public Function RoundtoUp( _
ByVal myNum As Double, _
Optional ByVal myFac As Double = 1#) As Double

RoundtoUp = (Fix(myNum * CDec(10# ^ myFac)) + IIf(myNum = Fix(myNum * CDec(10# ^ myFac)), 0, Sgn(myNum))) / CDec(10# ^ myFac)

End Function``````
 Number Digits RoundtoUp RoundUp (Excel) 0.0099, 1 Ok '0.1 '0.1 0.0099, 2 Ok '0.01 '0.01 0.0099, 3 Ok '0.01 '0.01 0.0099, 4 Error '0.01 '0.0099 -0.0714285714, 2 Ok '-0.08 '-0.08 -1.2345, 3 Ok '-1.235 '-1.235 1.01234012340125, 14 Error '1.01234012340126 '1.01234012340125

<tbody>
</tbody>

Could not debug the reason for such a behavior. What is a possible fix?

Last edited:

#### shg

##### MrExcel MVP
Via formula:

 A​ B​ C​ D​ 1​ Number​ Digits​ Result​ 2​ 0.0099​ 1​ 0.01​ C2: =--TEXT(A2, "0." & REPT(0, B2 - 1) & "E+0") 3​ 0.0099​ 2​ 0.0099​ 4​ 0.0099​ 3​ 0.0099​ 5​ 0.0099​ 4​ 0.0099​ 6​ -0.071428571​ 2​ -0.071​ 7​ -1.2345​ 3​ -1.23​ 8​ 1.012340123​ 14​ 1.012340123​

Via UDF:

Code:
``````Function SigDig(d As Double, ByVal n As Long) As Double
If n < 1 Then n = 1
If n > 16 Then n = 16
SigDig = Format(d, "0." & String(n - 1, "0") & "E+0")
End Function``````

#### Juggler_IN

##### Board Regular
UDF not working with other numbers. There is a mismatch wrt to Excel RoundUp.

Say, 1.49999999999999,0 with roundup = 2. With SigDig it gives 1. I am looking a RoundUp equivalent.

#### shg

##### MrExcel MVP
That formula/function rounds to specified significant diigits; did you want to round up? Sorry if I misunderstood.

#### Juggler_IN

##### Board Regular
Yes, looking for RoundUp, i tried addressing floating-point part by using CDec, yet with some numbers not getting the right answer.

#### shg

##### MrExcel MVP
I'd punt and use the Excel function:

Code:
``````Public Function RoundtoUp(d As Double, n As Long) As Double
RoundtoUp = WorksheetFunction.RoundUp(d, n)
End Function``````

#### Juggler_IN

##### Board Regular
This option I had but was looking for a custom UDF. Coudn't understand why the formula was failing in certain scenarios.
Code:
``[COLOR=#333333]RoundtoUp = (Fix(myNum * CDec(10# ^ myFac)) + IIf(myNum = Fix(myNum * CDec(10# ^ myFac)), 0, Sgn(myNum))) / CDec(10# ^ myFac)[/COLOR]``

1,078,437
Messages
5,340,270
Members
399,361
Latest member
Linford

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...