Not knowing exactly how the displayed as rounded (but not really rounded
numbers) are arrived at, I do have 2 more 'suggestions' that you could work
on. Both essentially involve a second worksheet.
1 If the user is just entering the numbers, just have a second tab modeled
after the first that emulates the entry tab, but with a display that looks
how you want.
2 Using VBA (that is currently beyond my capacity), when someone enters a
specific piece of data, use VBA to first take the entered data, enter on a
hidden sheet, and then change the display of the sheet entered on.

Oh, wait, I do have a 3rd suggestion. Get xl2007, I am fairly certain it can
do what you want (though not 100% positive). Wished I could have been more
help.
--
John C


"Tarfun" wrote:

Exactly. I want to change to display but not the stored number. I know how to
display to the nearest 1,000. I want to know how to display to the nearest
100 or nearest 10. Are those other format displays possible? If so, how.
--
Leo


"Gord Dibben" wrote:

Formatting changes only the display of the number.

No formatting can change the actual number.

You will use the ROUND function in formulas or event code to round the
numbers as you enter them.


Gord Dibben MS Excel MVP

On Fri, 22 Aug 2008 13:36:00 -0700, Tarfun
wrote:

John,
Thanks for your efforts, but I've already considered proceeding as you
suggested. That may work for someone skilled in navigating Excel, but it is
not something I could turn over to a clerk to use regularly for a display of
four or more columns, with 15 to 20 subtotals in each column. I'm not certain
I'd want to contend with that either.

In <Format<Cell<Number<Custom one can enter the following new number
format to round numbers to the nearest 1,000: #,",000"

That only changes the displayed number, but does not change the value stored
for computations. Seems to me that rounding a number to the nearest 100 or
nearest 10 should also be possible if only we knew the entries to make in the
Custom Number Format screen. Maybe someone else out there has an answer.

Thanks again.