CODEWITHSUNDEEP
excelcustom-formatting
pmackni
pmackni

Reputation: 313

Excel custom formatting positive/negative numbers with Thousand/Million/Billion (K/M/B) suffixes

I know how to do each of these individually (formatting for positive/negative/zero/text values and thousand/million/billion suffixes), but I don't know how to combine them.

Using this as my base for +/- values:

_(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)

I wanted to include the suffixes for the first two using this as a basis for using suffixes:

[<999950]#,##0,"M";[<999950000]#,##0,,"M";#,##0,,,"B"

This would effectively fill in the first two slots of the +/- formatting, replacing _(* #,##0_) and _(* (#,##0). The closest I can get working only ends up having a single conditional for the positive values, like so:

[<999950000]_(* #,##0,,"M"_);_(* (#,##0,,"M");_(* "-"??_);_(@_)

Does anyone know the best way to get something like this:

[<999950]_(* #,##0,"M"_);_(* [<999950000]#,##0,,"M"_);_(* #,##0,,,"B"_);<same for negative values>;_(* "-"??_);_(@_)

Any help would be appreciated, and while it would make me sad, I don't mind being told this is not possible, either. Thanks!

Upvotes: 0

Views: 2413

Answers (1)

pmackni
pmackni

Reputation: 313

Since it appears to not be possible with a one-stop solution (which while I think doing this without a one-stop is a little messy, but I also understand why they can't just magically understand every conceivable custom format iteration), I am opting for a two-step approach:

I will have 3 custom formats. One for the positive numbers with suffixes, another for the negative numbers with suffixes, and a third that is just the "standard" positive/negative number format (displayed in the question). I will then use a series of two or three conditional formatting rules to determine which of these custom formats will be displayed.

Personally, I am going to use the +/- format as the cell's format, then apply two conditional rules that change it to the two suffix variations, but I could see the argument for using conditional formats for all three.

Thanks for the feedback and the reminder that conditional formatting exists to aid with this very kind of issue.

Upvotes: 0

Related Questions