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
...
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