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?
Many thanks,
Kudos
[>=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?
Many thanks,
Kudos