Ask Your Question

Format numbers to hundreds?

asked 2017-06-30 18:08:19 +0100

kalliklles gravatar image

Is it possible to use the format codes to display numbers rounded to tens, hundreds, or thousands?

Say I have the number 11,234 and I want 11,200 to who in the cell, which format code would allow me to do that?

Notice that I know I can use ROUND() to round the number, but I want to keep them unrounded for further computations. Only visual display should be affected.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-06-30 19:31:38 +0100

Lupp gravatar image

updated 2017-06-30 19:33:03 +0100

Suppose this is about the usage of a column:

Better don't long for another lying format, but use a (probably later hidden) column for the actual values and another one for the rounded values and the respective display.

edit flag offensive delete link more


Actually, @Lupp's advise is better.

Mike Kaganski gravatar imageMike Kaganski ( 2017-06-30 21:05:43 +0100 )edit

answered 2017-06-30 18:19:27 +0100

You may only do that using thousand separator (and so "round" to thousands, no hundreds etc.)

edit flag offensive delete link more


Could you tell me which custom format would display 12,345 as 12,000? There no such examples on the page you linked, and all permutations of commas and #s I tried fail

kalliklles gravatar imagekalliklles ( 2017-06-30 19:41:06 +0100 )edit

You may use simply #,"000". That is, you output your zeroes as text.

Mike Kaganski gravatar imageMike Kaganski ( 2017-06-30 20:44:06 +0100 )edit


Lupp gravatar imageLupp ( 2017-06-30 20:51:10 +0100 )edit

Ah, that works, thanks. I hadn't though of using quotes. In fact #,",000" even keeps the thousand separating comma.

kalliklles gravatar imagekalliklles ( 2017-07-01 06:30:52 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-06-30 18:08:19 +0100

Seen: 65 times

Last updated: Jun 30 '17