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

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

Wed, 18 Jun 1902 08:00:00 GMT
Rounding whole numbers to the nearest 1,000 or 100,000 in Excel
Round(number, num_digits)

If num_digits is negative, rounding is to the left of the decimal point.

So to round your number to the nearest 1000:  =ROUND(1334979, -3)

Best,
ron

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

Wed, 18 Jun 1902 08:00:00 GMT
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

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

Wed, 18 Jun 1902 08:00:00 GMT
Rounding whole numbers to the nearest 1,000 or 100,000 in Excel
The =ROUND() function takes two arguments:  1.  the number to be rounded,
and 2.  the number of digits to round to.

If you put in a NEGATIVE number for the second argument, the rounding is to
the left of the decimal.  I.e., the second argument is actually the power
of ten that you want to round to.

Say your number is in cell a1.  The formula you want is "=round(a1,-3)"

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

Wed, 18 Jun 1902 08:00:00 GMT

 Page 1 of 1 [ 4 post ]

Relevant Pages