?
Solved

Excel - format number

Posted on 2011-10-21
6
Medium Priority
?
421 Views
Last Modified: 2012-05-12
Do you know how to customize in Excel to format a number in millions.   For example, I want to format (not round) the number 1,234,567 to 1,200,000.  

I don't want the actual number to round or truncate.
0
Comment
Question by:HNA071252
  • 2
  • 2
  • 2
6 Comments
 
LVL 25

Expert Comment

by:Brian B
ID: 37008790
Unless there is a format that can mask the value, it can't be one value and display another. You may have to hide the column containing the true values and then create another column with the ROUND() function. In you example, it would be =round(a1,-5). There is also roundup and rounddown if you always want it to round up or down, respectively.
0
 
LVL 3

Expert Comment

by:varontron
ID: 37008835
To do millions, you can use a custom format like this:   #,,",000,000"

Your question asks millions, but your example displays 100,000s.  Not sure how to do that.

Dave
0
 
LVL 25

Accepted Solution

by:
Brian B earned 750 total points
ID: 37024445
Do you always want to round down? If so, this format will display the value as millions, although I'm not sure if this is useful.

0.0,,

Will display 1234567 as 1.2.

Like I said, maybe not the exact answer, but hopefully gets you started. You can use the request attnetion link if you want to get other expert's opinions.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:HNA071252
ID: 37135335
I still not able to display 1,200,000 from 1,234,567

How do I request attention to this question?
0
 
LVL 3

Assisted Solution

by:varontron
varontron earned 750 total points
ID: 37135691
I've scoured and tested for solutions to your question.

I believe now it is not possible to do.  It seems the best you can do is limit display by sections  i.e.,
0,,

Open in new window

 will turn 1234567 into '1', but
0,

Open in new window

 will display '1235',
0,0,

Open in new window

will display '1,235' and
0,0

Open in new window

will display the whole number.  Any placeholder, 0,#,? will have the same effect.  Further, there is no pattern in formatting for changing significant digits to zeros.  For that, it seems, you must round.  (plenty of left-of-highest-digit and right-of-decimal padding options, though)

Here is one of many links to formatting syntax:  http://www.ozgrid.com/Excel/CustomFormats.htm

If you can explain your problem in terms of what your requirement is, (i.e., client provides x, wants to see y, can't change z) rather than asking about how to implement your chosen solution, perhaps we can figure out another way?
0
 

Author Closing Comment

by:HNA071252
ID: 37210282
Thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question