Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format cells: roundup for display only

    Hello:

    I need format cells to round up for display only but still contain the full precision original number for later post processing. I have not found a way to do that using the Custom Cell Format and obviously the roundup() or ceiling() functions won't work because they change the contents of the cell.

    I hope I don't need to duplicate the cells because that would force quite a bit of extra work.

    Does anyone have any advice?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    There isn't a format for always rounding up. Duplicating the cells would probably be easiest.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I know it's a bit silly, but you could add 0.5 to the cells, which has the effect of rounding up, then allow for this in later processing.

    Dangerous as you might forget, but avoids duplicate cells - just an idea.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    ARGold,

    This can be done using 2 User Defined Functions (UDF) and a Worksheet_Calculation event procedure.

    In a standard module paste the following code:
    Code:
    Public Adr As String, AdrVal As Double
    
    
    Public Function RndUP(num As Double, digits As Integer)
        Adr = Application.Caller.Address
        AdrVal = num
        RndUP = WorksheetFunction.RoundUP(num, digits)
    End Function
    
    
    Public Function V(rng As Range)
        Application.Volatile
        V = Worksheets("Hidden").Range(rng.Address)
    End Function
    In the Sheet module paste the following code:
    Code:
    Private Sub Worksheet_Calculate()
        On Error Resume Next
        Application.EnableEvents = False
        With Worksheets("Hidden")
        .Range(Adr) = AdrVal
        End With
        Application.EnableEvents = True
        On Error GoTo 0
    End Sub
    Here is how it works.
    1. Use the RndUP(number, num_digits) function as you would use the ROUNDUP(number, num_digits).

    Code:
    In cell D1 =RndUP(A1,0)    If A1=5.23 then D1=6
    In cell D2 =RndUP(A2,0)    If A2=4.75 then D2=5
    The UDF also writes the unrounded value to public variables then the Worksheet_Calculation event writes those variables to a hidden sheet called "Hidden" in the same exact cell as the formula. So in the above formulas, on the hidden sheet cell D1=5.23 and D2=4.75 and are available for recall.

    Now to recall the precision values for use in a calculation. Suppose you wanted to multiply cell D1 times cell D2. If you used the formula in F1 =D1 * D2, this would use the rounded values 6*5 and the product would be 30. However, the second UDF called "V" will use the hidden precision values instead. Change the formula in F1 to =V(D1) * V(D2) and the result will be 24.8425 (5.23 * 4.75)

    Recall1.png

    HTH,
    Maud
    Attached Files Attached Files

Posting Permissions

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