Having Trouble Showing Negative Millions as "##.# M" With Custom Text Format

[Aimee S.]

Hello, Folks.

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

I have been using this custom format for a while now (I have forgotten from where I nabbed it online, but it has served me well to-date in terms of showing thousands and millions as one-point decimals with a K and M for easier reading).

Is it possible to expand the rule set for this custom format to accommodate negative amounts less than -999999 so that instead of showing, say, - $1,657.2 K it correctly shows - $1.7 M? Or is the custom format too complicated, meaning the above is the limit of what Excel can handle?

Thanks for your time.
Aimee

 

[Scott Huish]

You can't do any more than that because there are only 4 spots for number formatting.

Without any conditions, they are by default:
Positive;Negative;Zero;Text

I don't believe your General at the end will do anything because that is the text spot.

 

[shg]

You could use conditional formatting to make it as complicated as you like.