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!

CodePudding user response：

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.