Custom formats to round numbers such as 16,465,123 to 16,500,000

D

davey888

Can you please provide a custom format that will format (for example)
16,465,123 to the nearest 100,000 (16,500,000)
 
V

via135

hi!

try this!!

B1: =MROUND(A1,100000) assuming your number 16,465,123 is in A1

-via135
 
D

davey888

Thanks. Unfortunately the function MROUND does not exist on my version of
Excel.

One thing I do not want to do is change the value - using the ROUND function
alters the value thereby affecting subsequent calculations.

The following format rounds to the nearest 1000

#,",000"

but I need to round to the nearest 100,000. Do you or anybody out there no
of workable solutions?

Ta
 
R

Ron Rosenfeld

Can you please provide a custom format that will format (for example)
16,465,123 to the nearest 100,000 (16,500,000)
Click to expand...


You cannot do this with formatting. The closest you can come with formatting
is to the nearest 10^n where n is a multiple of 3.

So to display to the nearest 10^6, for example:

Format/Cells/Number Custom Type: #,###,,",000,000"

To display how you specify, you will have to ROUND the number. This, of
course, would change the value for use in subsequent calculations.

e.g. =ROUND(A1,-5)

--ron
 
R

Ron Rosenfeld

hi!

try this!!

B1: =MROUND(A1,100000) assuming your number 16,465,123 is in A1

-via135
Click to expand...


Why use MROUND which requires installation of the Analysis Tool Pak (in
versions prior to Excel 12), when you can use the built-in function ROUND more
simply???

=ROUND(A1,-5)


--ron
 
M

MrShorty

Do you need all the zeros? A custom format code of 0.0,, will displa
16.5 (understood to mean 16.5 million) while retaining the underlyin
value
 
V

via135

of course..you are right!
thks for the advice..Ron!

-via135



Why use MROUND which requires installation of the Analysis Tool Pa
(in
versions prior to Excel 12), when you can use the built-in functio
ROUND more
simply???

=ROUND(A1,-5)


--ro
 
V

via135

of course..you are right!
thks for the advice..Ron!

-via135



Why use MROUND which requires installation of the Analysis Tool Pak
(in
versions prior to Excel 12), when you can use the built-in function
ROUND more
simply???

=ROUND(A1,-5)


--ron
 
D

davey888

Thanks everyone! I appreciate your help.
Dave

Ron Rosenfeld said:
You cannot do this with formatting. The closest you can come with formatting
is to the nearest 10^n where n is a multiple of 3.

So to display to the nearest 10^6, for example:

Format/Cells/Number Custom Type: #,###,,",000,000"

To display how you specify, you will have to ROUND the number. This, of
course, would change the value for use in subsequent calculations.

e.g. =ROUND(A1,-5)

--ron
Click to expand...
 
Top