Custom Number Format (hundred-thousands, Millions)
May 22, 2009
I have a giant sheet that culminates in a graph. It is for various regions. When used in a country with in-control currency, you end up with a nice healthy number that looks good on the graph. When our Zimbabwe office uses it with their crazy inflation and high prices, we get a crazy number that goes into scientific notation...
Anyway, my question:
Given a number like 4,200,000 or 6,500 or 345,123, I want to be able to show as few trailing numbers as possible, and give a nice K or B or D or however you abbreviate Quadrillion (kM? for Zimbabwe) after it.
And, if possible, I'd like to do it with number formats, but I am flexible so long as it doesn't use VBA.
The lookup table would be:
Sheet1 AB1<1,000As is21,000K31,000,000Mil.41,000,000,000Bil.5>1,000,000,000,000Tril. Excel tables to the web >> Excel Jeanie HTML 4
All numbers have 2 significant digits, and I want to keep 2, so if it ends up as 4,200,000, I want the answer to read 4.2 Mil, etc.
I tried doing this with a lookup, and couldn't handle it.
I am trying to write a custom cell format that would allow me to replace the comma with a space after the thousand's place. Therefore, 1 million would look like: 1 000 000 instead of 1,000,000. I do NOT want to change my regional settings to accomplish this.
When I write ### ### ### it accomplishes this, however, there is one small glitch. When you apply an "underline" you get the following result:
The underline does not lineup evenly with the "ones" digit until we reach 1 million. I want to write a universal format in which the underline lines up evenly regardless of the number.
I'm trying to write a formula for the following simple situation. Eventually I will need to use this formula for several hundred people with several hundred choices.
Like I said, this is a simplified example that I will eventually need to expand out to hundreds of people. Lets say I have 5 people, we will call them #1, #2, etc to #5 and I have 5 cars, red, blue, black etc. Person #1 gets his first choice, person number 2 gets second choice etc. But if person #2's first choice is the same as person #1's, obviously he can't have that choice and must go to his second choice. Some people may not have a preference and won't get assigned any color.
I want to show numbers on my excel spreadhseet in thousands. However, the challenge is I want to show negtive numbers in red within brackets. For instance, I have a number -104,784,089. I want to display it as (104,784) in red color. Similarly, I want to show 577,090 as 577. Is there a customer formatting I can set up in excel that do that?
I am trying to create a number format for formatting resistor value in their most commonly displayed form. For example 1,200 ohms would be displayed as 1.2kÙ and 5,000,000 ohms would be displayed as 5MÙ, and finally 5 ohms would be displayed as 5Ù. I have been able to create a format that formats any 2 contiguous formats but not all three.
for example: [>999999]###.0,,ÌÙ;[<1000000]###.0,kÙ; or: [>999]###.0,kÙ;[<1000]###Ù
both work.(other similar criteria work as well) But if I try to put 3 conditions describing the desired formatting I get and error (containing no useful info) as I try to close the formatting dialog. Is there a limit to the number of conditions that can be used. Or something else I'm overlooking.
is it possible to custom format a cell in order to:
a) Present 3.33333 as 3.33 (max. of 2 decimal digits or less) b) Present 3.333 as 3.33 c) Present 3.33 as 3.33 d) Present 3.3 as 3.3 e) Present 3 as 3 (NO zeros and NO decimal dot)
I know this works because I have seen it in a workbook at a client's office.
I need a custon number format to substiture an "em" dash for zero. An em dash is a hyphen with the width a the letter m. There is also the so called en dash tht is the width the the letter n. A search of the internet shows that to create an em dash in a cell that contains a 0 ("zero"), hold down the alt key and type 0151 on the numeric keypad. Typing 0150 insert a en dash.
I want to be able to set this up as a custom number format in the custom number dialog.
Instead of using conditional format, I would like to use a custom format to hide the number 1. Is this possible?
Basically only three things can be in this cell =IF(D8"",IF(D8="CIV",1.077,1),"")
I need to mask the 1, otherwise show the 1.077. The reason I am not using conditional formatting is because there is a condition on the cell already which applies a pattern style and a font will not match up to the pattern.
Is there any way to get a custom number format to display a multiple of the cell value? e.g. can I have a cell have the value 0.005 and display the value 50?
I have a number in a cell of 1025132.36. I need to scale it down to this number with this formatting: 1,025.13 (Two decimal places with comma separaters)
Basically it is dividing it by 1000 or scaling it by 3 (move the decimal place to the left three places). I cannot use /1000 in the cell and I cannot reference it to/from another cell. I must use something under Format/Cell. I was trying to create a custom format.
I saw in another forum thread to use #”.”###. This does the scaling but then I can’t make the number viewed with only two decimal places. Also when I tried to add comma separators for thousands it put a comma right before the decimal.
i am looking macro code to fixing value/number from custom number format (with dot mark criteria) as my data and will be as displayed fixing (what you see if what you get)....
2004.09.000907 (custom format) --------- 2004.09.000907 (after using macro) 1998.08.000001 (custom format)----------1998.08.000001 (after using macro)
if you press F2 in the sample number will be not visible dot (.) mark..
how to make/do that the number look like using custom number but fixed (remove custom number format)..
We have a macro that formats a report from our accounting program quite extensively (mostly moving text around). The last step is to select the entire worksheet and set the cell format to a custom setting #,##0.00_);(#,##0.00). We haven't been able to include this in the macro - we end up manually doing this step after the macro has completed. Is there any way to set this custom format as part of the macro?
I'm trying to create an Excel sheet to display MAC addresses in the following format -
00:00:00:00:00:00
However. the characters can be either letters or numbers. I've modified the "hh:mm:ss" template to accomodate the format I need, but it will not recognize letters for the obvious reason. Is there a wildcard character that I could use in this format or has anyone created a format that would meet my needs? If I can pull this off, it could mean a promotion.
I have been playing with custom formats recently and I'm wondering if there is anyway to make them wrap?
specifically I have several results that I have been formatting to show what they are related to
397235 visitors 392648 visitors 365487 visitors
I want these to display as visitors, but still be numbers so that they can be summed etc. The problem is that I need the text to wrap so that visitors is on the second line in the cell. unfortunatley all i get is a ########## error to show the cell is not wide enough even with wrap text turned on.
From what I find in Excel and from what the Microsoft website instructs, there isn't an obvious way to save a custom number format without having to create a template to store it.
Has a way been discovered to convince Excel to save a new number format and include it in the drop down selection for any workbook?
I would like to format a row of cells. I want each cell in the row to contain (and show) both an interger and text. The interger will range in value from one to ~10,000. The text will always be two characters long...but these characters will change from cell to cell.
Elsewhere in the spreadsheet I will have a cell (w/ a formula) that counts the values of the intergers in the row, but ignores the text in each cell.
This is my formula and it returns: -55 bps. =[ASK.xls]QTR!$AT$81&" "&"bps"
However, I want it to return (55 bps). I don't want to just add the parenthesis because in the future this formula could return a positive number. I tried to format the cell differently, but that did not work.
i want to know how to numberformat numbers (Currency) into thousands,lakhs. For example i m having amount in colmun D like 1239.00 i want to format the numbers in this column as Currency with thousand seperator and lakh seperator. like1,239.00 or 1,23,900.0
