Currency Format Request

[dbsbender]

Our company has a unique currency reporting format. Thousands are "M", Millions are "MM". I have crafted the format with one small issue. The negative currency does not do the "MM", only "M". Can anyone help me fix this formula? Oh, negatives should also be red and in parenthesis. Currency: [>=1000000] $#,##0.0,," MM";[>0] $#,##0.0," M";[Red]($#,###," M").

[Richard Buttrey]

Hi,

Please Login or Register  to view this content.

[dbsbender]

Thank you so much. Can the negative values be in red and in parenthesis also?

[Richard Buttrey]

Thank you so much. Can the negative values be in red and in parenthesis also?

They are on my system. Can you upload your workbook showing otherwise.

[dbsbender]

Thanks again. Let me see if I can make this work.

[dbsbender]

Good evening,
This is very nearly perfect and I thank you. I have only one issue. When entering a negative number (in the thousands like $-2500), it doesn't show in red or the parenthesis. Can you help?

$25,000.0 MM -($250,000.0 MM)
$2.5 M $2.5 M

In advance, I am very grateful.

Dianne S

[Richard Buttrey]

The difficulty I found was that the standard CFs use up to 4 sections separated by the ; .
These are Positive, Negative, Zeros and Text. The problem seems to arise when you need to specify a condition that a number must meet and use the [] syntax. It doesn't appear that you can mix and match so that once you've defined two sets of square brackets separated by the first semicolon, then the system is confused and thinks you've defined the negative syntax.

Not sure whether there is a way to avoid this.

[shg]

Instead of using custom numbers formats, you could use conditional formatting, which, since Excel 2007, allows you to include number formats, and to have as many as you like.

[Richard Buttrey]

Instead of using custom numbers formats, you could use conditional formatting, which, since Excel 2007, allows you to include number formats, and to have as many as you like.

Good thinking shg. Just shows what a fresh pair of eyes can bring and a lesson in not immediately getting locked in to methods suggested by the OP.

[shg]

Richard, I use custom number formats all the time, but have probably used CF to do something fancier twice in my life -- probably because I only pried my fingers off Excel 2003 in the last two or three years, but also because it doesn't pop to my mind either. And CF is expensive, performance-wise. So we should be forgiven in our dotage.