Millions thousands custom number formatting in Excel

Millions & Thousands Custom Number Formatting
To display 1,100,000 as $1.1M & 110,000 as $110.0K I've used the below Custom Number Formatting. 

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

This works great... unless the number goes negative. For instance a negative million will show as a negative thousand K. I've tried many different variations to attempt the negatives to appear correctly however the initial order Excel views them (positive; negative; zero; text) makes them all invalid. 

I suppose ideally what I'm asking is if you can combine Custom Number Format much like a AND OR function? Something to the effect of...

positive millions "M" or thousands "K"; negative millions "M" or thousands "K"; general

... because I would want to format for either/ or millions or thousands at the same time??

I hope this makes sense. Does anyone know of a way to do this? Or better yet... an alternative to my insanity that Excel will accept? 



try entering this custom format  [>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General

 Other excel answers



You can find similar Excel Questions and Answer hereunder

1) How can I get the day number of the year for a date - for example 5 for Jan 5, 33 for Feb 2?

2) Concatenate number with text but keeping in number format in Excel

3) Conditional formatting with if statement in Excel

4) How do I get the rank of a number in a list of numbers?

5) How can I set the fill color, font color and set number format of cell to date?

6) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

7) How can I find the number of working days between 2 dates given a holidays list?

8) In a Table , how can I add a Total Row that displays the maximum value in a column of numbers?

9) How can I get row count of filtered data?

10) How can I generate random numbers between 2 limits?


Here the previous and next chapter