Re: Extending the lesson
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.
