Results 1 to 7 of 7

Thread: Custom RoundUp function Error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Custom RoundUp function Error

    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
    Number Digits RoundtoUp RoundUp (Excel)
    0.0099, 1 Ok '0.1 '0.1
    0.0099, 2 Ok '0.01 '0.01
    0.0099, 3 Ok '0.01 '0.01
    0.0099, 4 Error '0.01 '0.0099
    -0.0714285714, 2 Ok '-0.08 '-0.08
    -1.2345, 3 Ok '-1.235 '-1.235
    1.01234012340125, 14 Error '1.01234012340126 '1.01234012340125

    Could not debug the reason for such a behavior. What is a possible fix?
    Last edited by Juggler_IN; Sep 2nd, 2019 at 11:23 AM.

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Custom RoundUp function Error

    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

  3. #3
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom RoundUp function Error

    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.

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Custom RoundUp function Error

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

  5. #5
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom RoundUp function Error

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

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Custom RoundUp function Error

    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

  7. #7
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom RoundUp function Error

    This option I had but was looking for a custom UDF. Coudn't understand why the formula was failing in certain scenarios.
    Code:
    RoundtoUp = (Fix(myNum * CDec(10# ^ myFac)) + IIf(myNum = Fix(myNum * CDec(10# ^ myFac)), 0, Sgn(myNum))) / CDec(10# ^ myFac)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •