Rounding Off

  • Thread starter Thread starter JP
  • Start date Start date
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
 
If you enter the 2 digits in A1:Axxx, put this formula in B1 =A1*10000 and
drag the formula down through Bxxx. Then format the cells as currency with 0
places of decimal.
If you enter the entire number instead of the first 2 digits there are
rounding methods that will accomplish what you want.

Tyro
 
Tyro and I read your question differently:
=ROUND(A1,-3) rounds to nearest thousand
best wishes
 
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?
 
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
 
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.
 
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
 
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
 
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.
 
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.
 
Back
Top