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

R

rcdude

New member
Joined
Sep 8, 2015
Messages
2
Reaction score
0
Points
0
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.
 
rcdude said:
The problem is that I do not want to manually edit each cell to add the Round function.
Click to expand...
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:
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,
 
rcdude said:
ex. suppose in sheet 1 one my formula is =Sum(A1:A5) but in sheet 2 is =Sum(B1:B2).
Click to expand...
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
 
You must log in or register to reply here.
Back
Top