I have a number format in Excel that rounds the display of numbers like 3,105,245 to $3.1M and 34,235 to $34K.
[>=1000000]$0.0,,"M";[>=1000]$0,"K";0
This works great except when the number is negative, I want the same kind of formatting except for a negative number. The number format above only works for positive numbers. I need one number format that works for both positive AND negative numbers.
I know about Conditional Formatting, but I would like to just use a number format if possible for reasons that would take to long to explain.
Best Answer
I've been reading around and from what I can tell, what you ask is impossible. Conditional formatting is limited to two conditional statements (source,source).
That doesn't mean you can't have this display the way you want, but will take a more complicated route, which requires that you put the following in another column.
More visually pleasing version (this is all strung together in a single cell, no carrige returns)
This blob of a formula does what it seems you want your conditional format to do. It assumes that the value you want to "format" is in cell A1. You can adjust the reference as needed to fit your spreadsheet.
I realize that this method is not solving the problem in the way you want. It also may not be usable depending on the reasons why your can't use conditional formatting. Without knowing the full details, I really can't know if this will work for you or not. If you are worried about duplicate data being viewed, you can always hide a column.
[EDIT]
Charts are different because you have to use macros to get it to do the really wild stuff. I ran across this page with some useful information on how to set stuff up. Looking over the "Arbitrary Axis Scale" page, it mentions that a third party has a free excel add-on that might do the trick. I haven't tested it, but it seems like it should allow you to change your axis scales to show the correct value, assuming you are using a line graph.
If you are using a bar, pie, or specialized graph. You'll probably have to turn the graph into a picture and then add the custom labels as needed. Not a path I would go down, but may be your only option.
[EDIT]
I hope this helps.