Cell Number Format to include Millions and Thousands

[jay_hl]

Hello

Im having some problem with a number format. This format gives me what I need (dollar signs, positives in black, negatives in red, zero's hidden)
_-[$$-en-US]* #,##0_ ;[Red]_-[$$-en-US]* -#,##0 ;;

However I would like to add to both positives and negatives that Millions are to 1 decimal place with " M", and thousands to the whole number and then " k". When I do this, I get an error saying Excel cannot use this
[>=1000000] _-[$$-en-US]* #,##0.0,," M";[>0]_-[$$-en-US]* #,##0," k";[>=1000000] _-[$$-en-US]* #,##0.0,," M";[Red][>0]_-[$$-en-US]* #,##0," k";;

This line does work for positive numbers (with M and k), but messes up for negatives and then displays zeros.
[>=1000000] _-[$$-en-US]* #,##0.0,," M";[>0]_-[$$-en-US]* #,##0," k";[Red]General

Any help would be appreciate if we can merge.

Jay

 

[ZMyrrh]

Hi Jay,

I learned that you will not be able to do what you want with the customer number format alone. You will need to use conditional formatting as well.

Here is what I did that worked with what I think you wanted.

For the positives I used: "Conditional Formatting" > "Format only cells that contain" > "Cell Value" > "greater than or equal to" > "0" with the format: [>=1000000] _-[$$-en-US]* #,##0.0,," M";[>0]_-[$$-en-US]* #,##0," k";General.

For the negatives I used: "Conditional Formatting" > "Format only cells that contain" > "Cell Value" > "less than or equal to" > "0" with the format: [Red][<=-1000000] _--[$$-en-US]* #,##0.0,," M";[Red][<0]_--[$$-en-US]* #,##0," k";General.

If you need any more help let me know!

Thanks!

Zach

 

[jay_hl]

Thanks very helpful