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!
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.