| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell will not format numbers correctly for a 13 digit custom barc. | Excel Worksheet Functions | |||
| How to format text and numbers as custom | Excel Discussion (Misc queries) | |||
| How do I make custom formats with superscript or subscript%3f | Excel Worksheet Functions | |||
| Custom Formats | Excel Discussion (Misc queries) | |||
| Custom Formats | Excel Worksheet Functions | |||
Linear Mode
