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

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

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

hi!

try this!!

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

-via135


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


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=522954

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

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



"via135" wrote:


hi!

try this!!

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

-via135


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


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=522954

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

On Wed, 15 Mar 2006 19:19:03 -0800, davey888
wrote:

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


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

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

On Wed, 15 Mar 2006 22:03:36 -0600, via135
wrote:


hi!

try this!!

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

-via135


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


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

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

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


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=522954

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

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


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=522954

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

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


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=522954

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

Thanks everyone! I appreciate your help.
Dave

"Ron Rosenfeld" wrote:

On Wed, 15 Mar 2006 19:19:03 -0800, davey888
wrote:

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


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