Rounding Off

J

JP

$248,071 $260,290 $261,640
$106,316 $111,553 $112,131
$0 $0 $0
$354,387 $371,843 $373,771

I need to enter all of the above numbers into a form that looks like
this:


xx 0,000

So, I have to enter the first two didgits. I need to round off the
rest. The first row would be:

$250,000 $260,000 $260,000

How would I construct a formula to do that?

Thanks
 
B

Bernard Liengme

Tyro and I read your question differently:
=ROUND(A1,-3) rounds to nearest thousand
best wishes
 
T

Tyro

Exactly what numbers are you entering? 248071 or 24? You say you enter the
first 2 digits. Are you really entering the entire number?
And what are your rounding rules?
 
J

JP

I already have the entire numbers, which are the result of formulas in
cells. I'm rounding to xx0,000. So I guess that would be to the
nearest 10,000?

Thanks for the help
 
J

JP

Thanks, I think this solves it except my formula would be
=Round(A1,-4)

Is there a way I can insert this formula into a cell that already has
a result from another formula?

If I have 248,361 in a cell, which is the result of say, c16-c15, can
I make the answer show up as 250,000 instead of 248,361 without having
to input the formula =round(A1,-4) into yet a different cell?

Thanks again.
 
G

Gord Dibben

How about a macro to add the ROUND to existing formulas?

Adjust the ",2" to your liking......presumably ",-4"

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
 
B

Bernard Liengme

No; but when you have copies this down the column do this:
Select all the cells with the ROUND formula
Use Copy
With cells still selected; Edit | Paste Special ->Values
Now the formulas are converted to number and you can delete the original
range
best wishes
 
R

RagDyeR

You could add the round function to the already existing formulas right in
the cell containing the original formulas.

For example:
=C16-C15
Change to:
=Round(C16-C15,-4)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thanks, I think this solves it except my formula would be
=Round(A1,-4)

Is there a way I can insert this formula into a cell that already has
a result from another formula?

If I have 248,361 in a cell, which is the result of say, c16-c15, can
I make the answer show up as 250,000 instead of 248,361 without having
to input the formula =round(A1,-4) into yet a different cell?

Thanks again.
 
J

JP

thanks, I figured that out finally after much trial and error. That
is exactly what I wanted to do. Thanks for the macro, whoever posted
that too. IT was also useful.
 
Top