Custom RoundUp function Error

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
168
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
NumberDigitsRoundtoUpRoundUp (Excel)
0.0099,1Ok'0.1'0.1
0.0099,2Ok'0.01'0.01
0.0099,3Ok'0.01'0.01
0.0099,4Error'0.01'0.0099
-0.0714285714,2Ok'-0.08'-0.08
-1.2345,3Ok'-1.235'-1.235
1.01234012340125,14Error'1.01234012340126'1.01234012340125

<tbody>
</tbody>

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

Some videos you may like

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
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
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
Joined
Nov 19, 2014
Messages
168
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
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
That formula/function rounds to specified significant diigits; did you want to round up? Sorry if I misunderstood.
 

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
168
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
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
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
Joined
Nov 19, 2014
Messages
168
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,090,138
Messages
5,412,685
Members
403,439
Latest member
KalengoPhiri

This Week's Hot Topics

Top