3

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.

Improve this question
2
  • I did a lot of looking around, and @Doltknuckle is right, you can have only two conditionals. I tried a lot of options, but couldn't find anyway to break that restriction.
    – Lance Roberts
    Jan 26, 2010 at 21:09
  • Thanks, Lance. I was hoping there might be something I was missing like being able to put an 'or' in one of the conditionals or something.
    – Daniel
    Jan 26, 2010 at 21:49

3 Answers 3

Reset to default
2

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.

=CONCATENATE(IF(A1<0,"-$","$"),IF(ABS(A1)>=1000000,FIXED(ABS(A1)/1000000,1),IF(ABS(A1)>=1000,ABS(A1)/1000,ABS(A1))),IF(ABS(A1)>=1000000,"M",IF(ABS(A1)>=1000,"K","")))

More visually pleasing version (this is all strung together in a single cell, no carrige returns)

=CONCATENATE(IF(A1<0,"-$","$"), IF(ABS(A1)>=1000000,FIXED(ABS(A1)/1000000,1), IF(ABS(A1)>=1000,ABS(A1)/1000,ABS(A1))), IF(ABS(A1)>=1000000,"M",IF(ABS(A1)>=1000,"K","")))

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.

Improve this answer
3
  • This is a clever solution. I like it. (I tried to upvote the answer, but I don't have enough reputation.) Unfortunately, it won't work for my current purpose because it changes the value of the cell into a string. I need it to be a number since I am using the value for a chart. The reason I can't use conditional formatting is because I am generating a spreadsheet programatically, and I get an error if I add more than three conditions.
    – Daniel
    Jan 26, 2010 at 20:45
  • You can use the original data for your chart, but I see what you mean about having to use number formats to get anything to display properly. Excel seems to have a problem charting negative numbers for some reason... Don't really know why. I did edit my post with some additional info on charts. I would take a look at the links in the edit and see if it solves your problem. It is possible that excel just can't do what you want. There are limits to what it can do.
    – Doltknuckle
    Jan 27, 2010 at 17:35
  • The PeltierTech site looks like good source for info on Excel charts. I ended up solving this problem by having my program apply a different number format depending on whether the number is positive or negative. It would have been more satisfying to come up with a pure Excel solution, but like you said that may not be possible.
    – Daniel
    Jan 28, 2010 at 15:06
0

You could change cell format by creating your own Worksheet_Change sub depending on the sign of the input number.

Improve this answer
0

From your description you seem to be limited by the number of conditional formats rather than not being able to use them at all (unless I misunderstdood)

You could use two custom formats like the one you have but for the positive and negative numbers separately, and a conditional format to switch to the negative version for negatives, so in one condition you are switching between your 6 formats for millions, thousands, small numbers, in positive and negative forms.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .