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

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]
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top