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
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



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  
 
 [ 4 post ] 

 Relevant Pages 

1. Need to stop number field from rounding to nearest whole number

2. Rounding to the Nearest 1,000

3. Rounding Numbers to the nearest 1000

4. Rounding numbers to nearest 1000?

5. Rounding numbers to nearest whole

6. Rounding to the nearest 1000

7. Rounding to nearest 1000

8. Rounding Calculation results up / down to nearest 100 or 1000

9. round to nearest 1000

10. Adding rounded numbers to equal a whole number


 
Powered by phpBB® Forum Software © phpBB Group