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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,356
Messages
5,413,983
Members
403,511
Latest member
Emmanuel John

This Week's Hot Topics

Top