Custom RoundUp function Error

[Juggler_IN]

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

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

[TABLE="width: 655"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Digits[/TD]
[TD][/TD]
[TD]RoundtoUp[/TD]
[TD]RoundUp (Excel)[/TD]
[/TR]
[TR]
[TD]0.0099,[/TD]
[TD]1[/TD]
[TD]Ok[/TD]
[TD]'0.1[/TD]
[TD]'0.1[/TD]
[/TR]
[TR]
[TD]0.0099,[/TD]
[TD]2[/TD]
[TD]Ok[/TD]
[TD]'0.01[/TD]
[TD]'0.01[/TD]
[/TR]
[TR]
[TD]0.0099,[/TD]
[TD]3[/TD]
[TD]Ok[/TD]
[TD]'0.01[/TD]
[TD]'0.01[/TD]
[/TR]
[TR]
[TD]0.0099,[/TD]
[TD]4[/TD]
[TD]Error[/TD]
[TD]'0.01[/TD]
[TD]'0.0099[/TD]
[/TR]
[TR]
[TD]-0.0714285714,[/TD]
[TD]2[/TD]
[TD]Ok[/TD]
[TD]'-0.08[/TD]
[TD]'-0.08[/TD]
[/TR]
[TR]
[TD]-1.2345,[/TD]
[TD]3[/TD]
[TD]Ok[/TD]
[TD]'-1.235[/TD]
[TD]'-1.235[/TD]
[/TR]
[TR]
[TD]1.01234012340125,[/TD]
[TD]14[/TD]
[TD]Error[/TD]
[TD]'1.01234012340126[/TD]
[TD]'1.01234012340125[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[shg]

Via formula:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Number​

[/td][td="bgcolor:#F3F3F3"]

Digits​

[/td][td="bgcolor:#F3F3F3"]

Result​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0.0099​

[/td][td]

1​

[/td][td]

0.01​

[/td][td]C2: =--TEXT(A2, "0." & REPT(0, B2 - 1) & "E+0")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

0.0099​

[/td][td]

2​

[/td][td]

0.0099​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

0.0099​

[/td][td]

3​

[/td][td]

0.0099​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

0.0099​

[/td][td]

4​

[/td][td]

0.0099​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

-0.071428571​

[/td][td]

2​

[/td][td]

-0.071​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

-1.2345​

[/td][td]

3​

[/td][td]

-1.23​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

1.012340123​

[/td][td]

14​

[/td][td]

1.012340123​

[/td][td][/td][/tr]
[/table]

Via UDF:

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]

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]

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

 

[Juggler_IN]

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

 

[shg]

I'd punt and use the Excel function:

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

 

[Juggler_IN]

This option I had but was looking for a custom UDF. Coudn't understand why the formula was failing in certain scenarios.

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