Axis Formatting Negative Numbers

[laguna92651]

I am using the following format code for the y axis of a line chart. I am shortening the axis to show 3M or 500K instead of $3,000,000 or $500,000. I can't get it to work with negative numbers, I get the full $3,000,000. Somewhere I read you can only do 3 formats in a formula. Is there a way to include negative numbers using this formatting?

[>999999]$#,,”M”;[>999]$#,”K”;$#

Thanks

[MrShorty]

Do you need a fourth section? If you don't need to worry about numbers between 1e5 and -1e5, there are only 3 regions to worry about: [>=1e6]0,,\M;[>-1e6]0,\k;0,,\M See if that will work.

[laguna92651]

That seems to have done the trick, thanks much.

1. How would I add [Red] to the negative numbers?
2. Is the \M the same as "M"?
3. What does your code do different than what I had?
4. Is there a way to permanently save the code for use with other workbooks?
5. How do i delete

useless code that I created while trying to get this going? Don't see delete function as in normal formatting.

Thanks again

[MrShorty]

Help file with an explanation of available custom number format codes: http://office.microsoft.com/en-us/ex...503.aspx?CTT=1

1-3: Adding a color to any section should be as easy as adding the color specification to the desired section. However, you should note that, in order to make +500K appear black and -500K appear red will add a fourth section to the number format code, which we have already established will not work. For formatting axis labels, you may have to choose between having negative numbers appear red and having numbers between -1E6 and +1E6 appear with the K instead of M. Or explore something much more complex (like John Peltier's "arbitrary axis technique" with code to set the formatting and coloring of the axis/data labels.

The main thing that I saw differently is that I assumed, from your original post, that you wanted to format everything between -1E6 and +1E6 the same (0,\K). Adding the color means that this observation is no longer true.

As noted in the "display both text and numbers" paragraph, the backslash \ can be used to precede a single character that you want displayed. It is the same as using quotation marks around the text you want displayed.

4-5: I really don't know. Back in 2002, I got used to simply entering format codes in the custom box, and never bothered with storing/deleting specific codes. I have not yet bothered understanding how 2007 does this differently, so I cannot help you here.

[laguna92651]

Thanks MrShorty, the code you provided worked great for me just wanted to better understand my options.
Al

[laguna92651]

Do you have an updated Microsoft link, the link you shared is dead, thanks.

[MrShorty]

It seems to work for me.

FWIW, the help file I linked to is the same help file that should have been installed with your office installation. You should be able to find that same help file by putting "custom number format codes" or other suitable search string into the help box (the question mark in upper right of the ribbon in Excel).

[laguna92651]

Okay thanks got it.