Custom RoundUp function Error

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
157
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
Joined
May 7, 2008
Messages
21,681
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
157
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,681
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
157
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,681
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
157
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]
 

Forum statistics

Threads
1,078,552
Messages
5,341,119
Members
399,420
Latest member
Kezra

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top