Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Formula Rounding

    Hi,

    I need the formula rounding for decimal points in complete spreadsheet, that means i don't want to enter in each cells the formula manually.
    Because the cells already exist calculation formulas, so how to do that?. The rounding will always greater than.

    1.43 will be 2.00
    27.87 will be 28.00

    Thanks.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,172
    Thanks
    19
    Thanked 99 Times in 88 Posts
    You want to use the CEILING() function. CEILING(A1,1) would round up to the next integer.

    Edited to add: Sorry, I didn't read the post completely. You want to universally change the formulas to include the CEILING() function.
    Last edited by gsmith-plm; 2015-12-10 at 09:50.
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Foncesca,

    This code will convert all Numerical Formulas Only in the current worksheet to include rounding to the next highest integer:

    Code:
    Option Explicit
    
    Sub ConvertFormulaToCeiling()
    
       Dim Rng As Range
    
       With Application.WorksheetFunction
       
           Sheets("Sheet1").UsedRange.Select
        
           For Each Rng In Selection
         
              If .IsNumber(Rng) And Rng.HasFormula Then
        
                Rng.Formula = "=Ceiling(" & Right(Rng.Formula, Len(Rng.Formula) - 1) & ",1)"
          
              End If
        
           Next Rng
       
       End With  'Application.WorksheetFunction
     
    End Sub  ' ConvertFormulaToCeiling
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    foncesa (2015-12-10)

  5. #4
    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
    Just FYI, you can replace this:
    Code:
    Right(Rng.Formula, Len(Rng.Formula) - 1)
    with just:
    Code:
    Mid$(Rng.Formula, 2)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following 2 Users Say Thank You to rory For This Useful Post:

    foncesa (2015-12-10),RetiredGeek (2015-12-10)

Posting Permissions

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