Formatting M K, and Red

[jxj]

Working on formatting Millions, Thousands and negative numbers. Positive numbers work great but the following does NOT generate RED format with negative values.

[>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";[Red]General

Any help would be appreciated
Using O365

 

[awoohaw]

I think your semi colons are causing the problem. The [Red] must be infront of the limiting expression. Additionally, I do not see in the documentation where you can have two different formats for the same formatting group (Pos, Neg, Zero, Text)
Here is teh Microsoft information page:

Number format codes - Microsoft Support

You can use the built-in number formats in Excel as is, or you can create your own custom number formats to change the appearance of numbers, dates, and times.

support.microsoft.com

 

[awoohaw]

I think your semi colons are causing the problem. The [Red] must be infront of the limiting expression. Additionally, I do not see in the documentation where you can have two different formats for the same formatting group (Pos, Neg, Zero, Text)
Here is teh Microsoft information page:

Number format codes - Microsoft Support

You can use the built-in number formats in Excel as is, or you can create your own custom number formats to change the appearance of numbers, dates, and times.

support.microsoft.com

I can get this to work, but it then won't let me add formatting for Neg, Zero, or Text...
It did allow two numeric formats but I think it has something to do with the semi colons

[Red][<1000000]$#0.00," K";[Green][>=1000000]$#0.00,," M" So, it allows 2 formats for numbers above zero, but I think it then says you can't format negative or text after that. It does not go into detail on that in the link I provided. I'm at a loss
I can type the word General in the 3rd position, and it is 'accepted' by excel, but it still displays like the <1000000 group. Very hard to figure out.

 

[awoohaw]

Here is another microsoft page. This page has a chart about 1/5 the way down that explains the conditional formats.
It says there can only be two of these formats.. so there are no further sections of how to format the cells. Which I guess means the neg,zero, and text portions are not available and only the two conditional rules are available. In other words, it looks like everything after the 2nd format is ignored.

Microsoft Excel conditional number formatting

Microsoft Excel conditional number formatting

techcommunity.microsoft.com

 

[jxj]

Thank you all for trying. the 2 format limitation is disappointing

 

[awoohaw]

Thank you all for trying. the 2 format limitation is disappointing

You could probably do it with VBA. But, I am not VBA proficient