how do i add more to a formula without going into each cell?

O

orangie

I have a value in a cell, say A2 =5.62345. And I want to add a formula to
this value in this cell, basically =round(5.62345, 2), but i have 30+ cells
with values and I don't want to go into each cell individually. is there
anyway to avoid this?

Thanks!
 
F

Farhad

Hi,

you can do it by using another column like below:

A B
1 0.5845 =round(A1,2)
2 12.69585
3 15.65824
4 0.52548
..
..
..
and then copy the fomula in B1 down to where ever you want.

Thanks,
 
T

Toppers

You cannot have a value AND formula in the same cell.

Use a helper column with =ROUND(A2,2) ... copy down ... and then Copy, Paste
Special ==> Values if you want to remove the formula.

HTH
 
O

orangie

Thanks. But I'd like to keep the original number in the formula, and not A1
or A2, etc.

Thanks.
 
F

Farhad

try this:
and then copy the fomula in B1 down to where ever you need
then copy and paste special > value the cells that you made ( column B) in
to the another column and then active the tird column ( say column C) go to
menu: Tools> Text to column and flollow instruction.

hope this works for you.

Thanks,
 
D

Dave Peterson

You could use a macro. Select the cells to fix and run this:

Option Explicit
Sub testme()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Formula = "=Round(" & myCell.Value & ",2)"
Next myCell
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
O

orangie

I'm trying to use this macro but am having an application, unknown object
error:

Option Explicit
Sub round_made_easy()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Formula = "=Round(" & myCell.Formula & ",2)"
Next myCell
End Sub

Now when I try the above it gives me an application error.
But with the following code that you gave me it works great.

Option Explicit
Sub round_made_easy()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Formula = "=Round(" & myCell.Value & ",2)"
Next myCell
End Sub

why won't it work for a cell that is =1258/4569871 and keep the 1258/4569871
in the cell's formula of =Round(1258/4569871, 2) ???

Thanks!
 
B

Bob Umlas

because the formula includes the "=", so you'd wind up with
=ROUND(=1258/4569871,2) and Excel doesn't like the 2nd equal sign!
 
G

Gord Dibben

orangie

Try this version which preserves the formula in the cell.

Sub RoundAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & "," & "2" & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP
 
Top