Rounding integers to thousands (2000)

[sgthomas98]

Is there a way to round integers either up or down - whichever is traditionally appropriate - based on hundreds, thousands, etc.? For example, if we have a number such as $54,856,400.00, is there a function that would round this number to $54,900,000?

Thanks in advance for your help!

[HansV]

Say the non-rounded amount is in A1. In B1, enter the formula

=ROUND(A1,-5)

to round to the nearest multiple of 100,000.

If you want to display thousands or millions (not tens or hundreds etc.), you can use a custom format in the cell itself: <code>#,##0,</code> will show thousands (54,856) and <code>#,##0,,</code> will show millions (55)

[sgthomas98]

Sometimes the numbers I want to round are the result of adding two or more other cells together. Example, cell C1 displays 54,856,000 which results by adding A1 and B1 (=A1+B1). I want the result in C1 to be rounded, but when I use the formula you just indicated, the result shows up as "-" (sans the quotes). Why is this?

[HansV]

The formula would be =ROUND(A1+B1,-5)

Note: you should *not* combine such a formula with one of the custom formats I described - it's either a ROUND formula or a custom format, not both.

[macropod]

If you don't mind using results that are expressed thousands without the 0s, then, instead of rounding off the number, you could use a custom number format.

For example, if you use Format|Cells|Number|Custom and input:
#,
you'll get a result that's rounded off to the nearest thousand (ie: 123456789 becomes 123457) and
[tab#,000,