Rounding whole numbers to the nearest 1,000 or 100,000 in Excel

Use the ROUND function with a negative number of decimal places, e.g.,

=ROUND(A1,-6)

Or you can use the MROUND function.

=MROUND(A1, 1000000)

MROUND is more flexible because it can round to the nearest multiple

of any number, not just a power of 10, e.g.,

=MROUND(A1, 5)

MROUND is part of the Analysis Tool Pack, so you must have this

installed (Tools menu, Add-Ins, check Analysis Tool Pack).

Since you mentioned custom formatting, always remember that custom

formatting simply changes the way the value is displayed, not the

actual value itself. The ROUND and MROUND functions change the actual

value.

Cordially,

Chip Pearson

http://home.gvi.net/~cpearson/excel

Quote:

> I have Excel 97 and I cannot find a way to round a whole number to

the

> nearest 1,000 or 100,000. Example I want to round a column of

numbers that

> are in the millions.

> 1,334,979 to 1,335,000 etc.. I tried the Round function roundup and

down,

> Floor, and cell formatting custom but have had no positive results.

I know

> there is a simple way that I am overlooking. Does anyone know?

> Thanks