# 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:

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### 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]``