I have a column with numberical values that range from 0 to 1,000,000,000+. I would like to format the cells to abbreviate based on their value. So far I have this:

[>999999999]0.0,,," B";[>999999]0.0,," M";0," K"

and that works, but I'd like to add in a condition for anything less than 1000 to show up as NA. So far nothing I have tried works. Any ideas?

2 Answers

For Excel formatting with user specified criterion conditions the Alternative Section Arguments is used with a maximum of three sections rather than the default Standard Section Arrangement with its four sections.

(Source: SumProduct Pty Ltd.)

Multiple Number Formatting

Standard Section Arrangement (min 1 - max 4)

# of Sections - Values in sections

1 - All numerical values
2 - Non-negative numbers; negative numbers
3 - Positive numbers; negative numbers; zero values
4 - Positive numbers; negative numbers; zero values; text

Alternative Section Arguments (min 1 - max 3):

# of Sections - Section Details

1 - All numerical values
2 - Numbers meeting criterion; All other values
3 - Numbers meeting first criterion; Numbers meeting second criterion
. . . which do not meet the first criterion; All other values

I don't think it's possible to add any more conditions to the custom number format already used. One way is to add a conditional formatting with a custom number format such as [Red]"NA";[Red]"NA";[Red]"NA";[Red]"NA" with a formula like: =OR(NOT(ISNUMBER(A1)),A1<1000) (assuming that the cell is at A1)