Discussion on:
View:
Show:
How would it look if I was showing a trend analysis and some of the numbers had negative trends to display in millions?
Based on the example above, negative values would appear using the last format mask string ($#.##,," M") will appear with a leading minus sign, a floating dollar sign and the number -- e.g. -1234567890 would be:
-$1234.57 M
Note that there is no comma separating the billions from the millions.
Below are three options for negative values corresponding to Excel's
"standard" Currency (with floating dollar sign and either a leading dash or parentheses) and Accounting (fixed dollar sign and parentheses) format options. All three include "floating" commas to separate millions from billions, trillions, etc.
Using -1234567890 as the test value:
Currency format with a dash in front of negative values (note that a separate format mask for negative values is not used in this example):
$#,##0.00,," M"
$1,234.57 M
The "#,##0.00" portion insures that values less than 1 million will always display a 0 in the millions place along with the 2 places to the right of the decimal. The inclusion of the comma will provide separators between millions, billions, etc.
Currency with parentheses around negative values:
$#,##0.00,," M";($#,##0.00,,)"M"
($1,234.57)M
Note that the string following the semi-colon -- i.e. ($#,##0.00,,)"M" -- is the section that specifies the mask for negative values. Also note that the space precediing the "M" character in the positive value mask is replaced by the closing parenthesis in the negative value mask in order to preserve alignment of positive and negative values.
Accounting style (i.e. fixed "$" and parentheses):
_($* #,##0.00,," M";_($* (#,##0.00,,)"M";_($* "-"_)
$ (1,234.57)M
Note the 3rd mask string above -- _($* "-"_) which specifies the mask for zero values. Similar to Excel's default Accounting mask, this displays a dash preceded by a fixed dollar sign to indicate a zero value (as opposed to a blank cell). The "M" character is not displayed for zero values.
Of course, these are only 3 of many options -- none of which use the color option which can be added to the front of each section of a custom mask -- e.g.:
_($* #,##0.00,," M";[Red]_($* (#,##0.00,,)"M";_($* "-"_)
which will output negative values in red.
Way too long of a response I realize but hope it helps.
-$1234.57 M
Note that there is no comma separating the billions from the millions.
Below are three options for negative values corresponding to Excel's
"standard" Currency (with floating dollar sign and either a leading dash or parentheses) and Accounting (fixed dollar sign and parentheses) format options. All three include "floating" commas to separate millions from billions, trillions, etc.
Using -1234567890 as the test value:
Currency format with a dash in front of negative values (note that a separate format mask for negative values is not used in this example):
$#,##0.00,," M"
$1,234.57 M
The "#,##0.00" portion insures that values less than 1 million will always display a 0 in the millions place along with the 2 places to the right of the decimal. The inclusion of the comma will provide separators between millions, billions, etc.
Currency with parentheses around negative values:
$#,##0.00,," M";($#,##0.00,,)"M"
($1,234.57)M
Note that the string following the semi-colon -- i.e. ($#,##0.00,,)"M" -- is the section that specifies the mask for negative values. Also note that the space precediing the "M" character in the positive value mask is replaced by the closing parenthesis in the negative value mask in order to preserve alignment of positive and negative values.
Accounting style (i.e. fixed "$" and parentheses):
_($* #,##0.00,," M";_($* (#,##0.00,,)"M";_($* "-"_)
$ (1,234.57)M
Note the 3rd mask string above -- _($* "-"_) which specifies the mask for zero values. Similar to Excel's default Accounting mask, this displays a dash preceded by a fixed dollar sign to indicate a zero value (as opposed to a blank cell). The "M" character is not displayed for zero values.
Of course, these are only 3 of many options -- none of which use the color option which can be added to the front of each section of a custom mask -- e.g.:
_($* #,##0.00,," M";[Red]_($* (#,##0.00,,)"M";_($* "-"_)
which will output negative values in red.
Way too long of a response I realize but hope it helps.
To me it would be clearer if the 10^6 numbers had a leadin zero e.g. $0.15 M instead of $.15 M, it would be easy to miss the first place and the decimal point without a leading 0, and possibly misreading the .15 as a 15. Especially for us older folks with poor eyesight!
Change the first # to 0 -- $0.##,, "M" -- be sure to test it well, but that should work for you.
I almost skipped over this one 
I'm glad I didn't.
This is also useful for anyone who has to deal with government data, particularly macroeconomic data.
For the U.S., much of this data is already in trillions. That's 13-15 digits, and yet for most government data only 2-4 of them are actually significant digits. So, for my purposes, I'll also add a custom format for billions and for trillions.*
I actually had to use this sort of simplification for a group yesterday. I don't want a fence on the Mexican border, but some people do. But, the common complaint is that it would be too expensive: a common price tag is $15,000,000,000. People can't digest this number. So I pointed out that this is $15B and our government budget is around $3,600B ... so that if it's something you really wanted it's just a drop in the bucket.
* We are within a few years of getting some fairly common statistics needing to be denomination in quadrillions (thousands of trillions).
I'm glad I didn't.
This is also useful for anyone who has to deal with government data, particularly macroeconomic data.
For the U.S., much of this data is already in trillions. That's 13-15 digits, and yet for most government data only 2-4 of them are actually significant digits. So, for my purposes, I'll also add a custom format for billions and for trillions.*
I actually had to use this sort of simplification for a group yesterday. I don't want a fence on the Mexican border, but some people do. But, the common complaint is that it would be too expensive: a common price tag is $15,000,000,000. People can't digest this number. So I pointed out that this is $15B and our government budget is around $3,600B ... so that if it's something you really wanted it's just a drop in the bucket.
* We are within a few years of getting some fairly common statistics needing to be denomination in quadrillions (thousands of trillions).
I also see that this format automatically rounds to the closest digit (to be shown).
How does it do that?
How does it do that?
This was exactly what I was looking for! Saved me a lot of time! Thanks for sharing!!
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
