Adding Round funciton to handful of numbers

Rick Rothstein" wrote:
Okay, I'm confused again.

You're fine. Take a deep breath. I think you are getting confused by
postings that are crossing on the Internet, as well as other factors that I
will not mention to be polite .

In the posting timestamped 12:14p, Jamie explained exactly what is needed,
to wit: given a cell with a constant, change it to a formula of the form
=ROUND(constant,-2).

In the posting timestamped 12:23p, you provided exactly the right solution.
Problem solved. End of thread. Whew!

But to explain the confusion....

In the posting timestamped 12:27p, Jamie responded to your 12:10p response
to me in which you acknowledged that formatting alone would not solve the
problem.

Jamie then tried to explain __kind__ of solution he/she is expecting by
showing a solution to a __previous__ problem.

In the previous problem, someone had a formula in the cell, not a constant,
so of course the solution used MID(...,2,...) to strip off the formula's "="
to form the new formula, =ROUND(formula,-2).

Jamie misspoke when he/she wrote that the previous formula "does exactly
what I need". Of course, that formula does __not__ work when there is a
constant in the cell because there is no "=" to strip off. That is why
Jamie was seeking a solution to "this posting" (i.e. the topic of this
thread).

Arguably, the simplest solution is to change MID(...,2,...) to
MID(...,1,...). But of course, Rick's solution is more straight-forward for
that case.

Rick, I hope I have resolved your confusion.

Jamie. I think it would be sufficient for you to acknowledge that Rick's
posting timestamped 12:23p is all you need, and the problem is solved.


----- original message -----

"Rick Rothstein" wrote in message
...
This macro does exactly what I need but enters the =Round() formula
around a
formula I already have in the cell. For example if I had the formula
=A25*G55
in a cell the above macro would change that cell formula to
=Round(A25*G55,-2).

Okay, I'm confused again. Your original post said your cells had "raw
data" which I did not take to mean there were formulas in the cell. Now
you are mentioning formulas whereas each of your previous postings showed
constant values. And you say the code you just posted does what you want,
but obviously it doesn't (your example does not clarify what you are
actually looking for) or you wouldn't have posted in the first place.
Instead of adding new information with each post, do the following... show
us a sample of what is in a cell now... that is, what is in the Formula
Bar and what is displayed in the cell, then show us exactly what you want
to be showing in the Formula Bar and cell *after* the macro has run.

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
Take a look at the below Macro someone wrote for me a little while back:

Sub addround()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula And IsNumeric(cell) Then
cell = "=ROUND(" & Mid(cell.Formula, 2, 1000) & ",-2)"
End If
Next cell
End Sub

This macro does exactly what I need but enters the =Round() formula
around a
formula I already have in the cell. For example if I had the formula
=A25*G55
in a cell the above macro would change that cell formula to
=Round(A25*G55,-2).

Does that help anyone come up with macro so solve the problem of this
post?

"Rick Rothstein" wrote:

Okay, I think I'm up with what she wants now. I missed the...

ROUND(88888,-2) == 88,900

example in the OP's response to p45cal and simply thought the -2 was a
typo
in the OP's response to me.

To Jamie: I don't think you can do what you want with formatting or with
a
macro. There is no format for showing a number in hundreds and a macro
cannot make one up. To show a number in the format you want, that number
*must* be modified (divided by 100) and that would break your other
condition to show the original number in the cell. Excel can show
numbers in
thousands (and millions, billions) and still keep the original number in
the
cell, but not when the rounding is not a multiple of 3.

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
"Rick Rothstein" wrote:
you can just use normal cell formatting.

That's what I thought, too; but I'm drawing a blank. What numeric
format
displays the equivalent of ROUND(...,-2) -- i.e. rounds to hundreds?


Okay, the fact that you want to retain the original number and only
show
the rounded value is different than I originally interpreted your
response.

Yeah, Jamie is feeding his/her requirements to us in pieces. See the
response to "p45cal". Specifically, Jamie writes: "I would also like
to
make the macro so that I can highlight any cell either by hitting
shift or
holding down ctrl and the macro will apply to all selected cells".

Sounds like Jamie is hoping for some kind of event macro. Any ideas?

I would opt for a "button" (control) that invokes the macro after
making
the cell selection.

But then again, I do not trust my interpretation of Jamie's
requirements.
Personally, I would still opt for a formatting solution, unless I am
crafting the worksheet for others to use.


----- original message -----

"Rick Rothstein" wrote in
message
...
Okay, the fact that you want to retain the original number and only
show
the rounded value is different than I originally interpreted your
response. I'm thinking, as long as I understand what you want
correctly,
that you can just use normal cell formatting. Select all the cells
you
want to round (whether they current have values in them or not...
think
of the future possibilities), click Format/Cells in the menu bar,
select
the Number tab on the dialog box that comes up, select "Number" from
the
Category List and choose the number of decimal places you want all
your
numbers rounded to, then click OK. When you go back to your sheet,
any
numbers in those cells you selected originally will now display with
the
number of decimal places you picked, but the actual value in the
cells
will not be changed.

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
I would like the have my number normally rounded. For example, 88,888
would
round to 88,900. As if I were to have the following formula:
=Round("88888",-2).


"Rick Rothstein" wrote:

I think you will need a macro to do this. How did you want your
numbers
rounded (round up, round down, round to nearest interval, Banker's
Rounding,
normal rounding, to a set number of decimal places possibly coupled
with one
of the previous methods, some other way)?

--
Rick (MVP - Excel)


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number
and
was
wondering if there is a formula or macro I can run to accomplish
this. All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded
and
then
run
the formula/macro.

Let me know if this is possible.

Thanks

Hello Jamie,

Maybe

Sub addround2()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula And IsNumeric(cell) Then
cell = "=ROUND(" & cell.Text & ",-2)"
End If
Next cell
End Sub

You have raw data and you do not want to change any formula (or apply
this macro repeatedly), I guess.

Regards,
Bernd