Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting

[parkerbelt]

So, I'm using custom formatting to show -$1,200,000 as -$1.2M, but Excel is showing it as -$1200K.

Here's the custom formatting that I'm using:
[<999950]$0,"K";[<999950000]$0.0,,"M";$0.0,,,"B"

The weird thing is that when I do a positive number, like $1,300,000, it shows up as $1.3M, which is what I want.

Negative numbers are the only one's affected.

Does anyone know how to fix this?

 

[RoryA]

Any negative number is less than 999950, so your first format will apply. You will have to use a conditional format to apply separate number formats based on the values, as you can only have 2 condition sections in a custom number format.

 

[njimack]

That's because a negative number is less than 999950. Custom number formats are limited to four zones (typically positive, negative, zero and text) so you won't be able to achieve what you're trying to do.
I'd suggest using the TEXT function in a helper column, or using VBA to modify the format of each cell.

 

[parkerbelt]

Thanks guys! You wouldn't happen to know the conditional formatting formula that would make that happen or some other way to accomplish what I'm trying to do, would you?

 

[RoryA]

Apply a CF using Cell Value is < 0 and use that custom number format but reversing the two conditions and negating both values.

 

[parkerbelt]

Apply a CF using Cell Value is < 0 and use that custom number format but reversing the two conditions and negating both values.

That worked for formatting the number, but the chart that I have built off of that number changed to the un-abbreviated number. Is there a way to fix that?

 

[parkerbelt]

I figured it out. Had to change the number format to custom in the chart

 

[parkerbelt]

Thanks for your help!!