Results 1 to 4 of 4

Thread: How do I add the ROUND function to an existing formula?

  1. #1

    How do I add the ROUND function to an existing formula?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    What I want to do is, without disturbing the contents existing formula in each column - I would like to apply the Round function to it.

    And existing formula looks something like this:
    =Sum(A1:A5)

    I want to replace the above by:
    =ROUND(SUM(A1:A5),2)

    The problem is that I do not want to manually edit each cell to add the Round function.

    Please suggest. Thanks in advance.

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by rcdude View Post
    The problem is that I do not want to manually edit each cell to add the Round function.
    You did not write which Excel is installed. I can show you, for Excel 2007 and newer

    Excel Options => Advanced menu => Display Options for this Worksheet => you enable this option "Show formulas in cells instead of their calculated results"
    Close Excel Options

    Now you have a worksheet display formulas instead of results
    Click the Find & Replace button => Replace command
    In the Find field, type => =SUM(A1:A5)
    In the Replace With field, type => =ROUND(SUM(A1:A5),2)

    Click on the Options button (expand DialogBox)
    Within field: Sheet
    Search field: By Rows
    Look in field: Formulas
    Enable the "Match case"
    Click the Replace All button

    This action will find =SUM(A1:A5) and replace all formulas specified

    [EDIT]
    Note! Please first copy your file and then try
    Last edited by navic; 2015-09-08 at 05:52 PM.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Hi Navic,

    Thanks for your reply.

    I am using Excel 2013. i just give the one example my formula range is not the same for the all cells.

    The above solution apply only when my formula range was same.

    ex. suppose in sheet 1 one my formula is =Sum(A1:A5) but in sheet 2 is =Sum(B1:B2).

    So i just want to insert round function in one cell and then copy to all formula without changing there range.

    Hope i am able to understand you my query.

    Thanks,

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by rcdude View Post
    ex. suppose in sheet 1 one my formula is =Sum(A1:A5) but in sheet 2 is =Sum(B1:B2).
    Search Google for VBA solution or Find/Replace with wildcards

    like

    Code:
    Sub ReplaceFormula()
        Dim cell As Range
        For Each cell In Range("A1:AA100") 'yoru range where is formulas
            cell.Formula = Replace(cell.Formula, "SUM(", "ROUND(SUM(")
            cell.Formula = Replace(cell.Formula, ")", "),2)")
        Next cell
    End Sub
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

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