how to add an additional formatting formula to an existing formula

How can I add a formatting formula round,2 to an existing formula?  I have a 2000 line spreadsheet that I need to add rounding to an existing calculation.  I don't want to have to edit each line, and using the special formatting (0.00) doesn't allow me to pass the formatting when it is transferred via an import process.
kgittingerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
Normally you can copy the formula down the column, so assuming your first formula on row 2 just alter that to

=ROUND(existing_formula,2)

[omitting the = at the start of existing_formula]

and then you can copy down the column - if you have continuous data in an adjacent column you can just double-click the "fill-handle" and it will populate automatically all the way down. The "fill-handle" is the black "+" you see if you put the cursor on the bottom right of the cell.

Another way to fill the whole range is like this:

Change first formula and select that cell
In box above A1 type the whole range that the formula needs to populate (including the first cell), e.g. B2:B2000 and press ENTER and that whole range gets selected
Use CTRL+D to "fill down" the formula into that range

regards, barry
0
kgittingerAuthor Commented:
Unfortunately the formulas are not consistent from line to line, so I cannot drag down the formulas.  They are calculating off of other spreadsheets.  Right now what I am doing is hitting F2 entering round( in the beginning, and ,2) at that end.  Tedious...
0
ChloesDadCommented:
Another way would be to use a small macro

By using this method it will not matter what the original formulae was

Sub Macro1()
'
' Macro1 Macro
'

'

Dim Column As String

Column = "D"
For Row = 1 To 5
    Range(Column & Format$(Row, "0")).Formula = "=ROUND(" & Mid$(Range(Column & Format$(Row, "0")).Formula, 2) & ",2)"
Next Row
  
  
End Sub

Open in new window

This has the advantage that its just a case of running the macro when you want to apply the new forumlae
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.