Any way to round to denominations of 5000 using number format code

C

consumer

I'ld like to have numbers be shown as rounded to the nearest 5000.

Thus, if a cell has the value 92,345, I would like it to show 92,000. I
want to do this with the format codes...and NOT with a worksheet fxn if at
all possible.

Also, is there any way to modify this so that it would always round up or
down?

Thanks.
 
R

Ron Rosenfeld

I'ld like to have numbers be shown as rounded to the nearest 5000.

Thus, if a cell has the value 92,345, I would like it to show 92,000. I
want to do this with the format codes...and NOT with a worksheet fxn if at
all possible.

Also, is there any way to modify this so that it would always round up or
down?

Thanks.
Click to expand...

Hmmm. 92,345 rounded to the nearest 5000 would be 90,000, not 92,000.

In any event, using number formatting, you can round to the nearest thousand,
but I know of no way to round to the nearest 5,000.

If you want to show the result rounded to the nearest thousand, then the custom
format #,"000" should do it.


--ron
 
S

sert

Ron said:
Hmmm. 92,345 rounded to the nearest 5000 would be 90,000, not 92,000.

In any event, using number formatting, you can round to the nearest thousand,
but I know of no way to round to the nearest 5,000.

If you want to show the result rounded to the nearest thousand, then the custom
format #,"000" should do it.


--ron
Click to expand...
My mistake....92,345 -> 90,000.

Yeah, I know how to do to do nearest thousand...but I was hoping there
was a way to stretch this capability a little.

Thanks.
 
M

Myrna Larson

Maybe you could insert a helper column with this formula

=ROUND(A1/5000,0)*5000

Then hide the column with the actual data.
 
Top