Millions & Thousands Custom Number Formatting

[Kudos]

To display 1,100,000 as $1.1M & 110,000 as $110.0K I've used the below Custom Number Formatting.

[>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General

This works great... unless the number goes negative. For instance a negative million will show as a negative thousand K. I've tried many different variations to attempt the negatives to appear correctly however the initial order Excel views them (positive; negative; zero; text) makes them all invalid.

I suppose ideally what I'm asking is if you can combine Custom Number Format much like a AND OR function? Something to the effect of...

positive millions "M" or thousands "K"; negative millions "M" or thousands "K"; general

... because I would want to format for either/ or millions or thousands at the same time??

I hope this makes sense. Does anyone know of a way to do this? Or better yet... an alternative to my insanity that Excel will accept?

Many thanks,
Kudos

 

[lenze]

Opps!!
Need to change a few things to the above. The format for Style2 should be

[<= -1000000]-$#,##0.0,,"M";[<0]-$#,##0.0,"K";General

Also, the code line

Case Is > -1000000

should be

Case Is > 0

Sorry about that!!

lenze

 

[dmj120]

Hi and welcome to the board!!
Try changing your 2nd condition.

[>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";General

lenze
Edit: you might want to color your negatives Red

[>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";[Red]General

Edit2: How are the numbers obtained? By formula, a Query or user entry?

Thanks to google.... I found this works perfectly.

One question: How would you read/say this? ie: for IF formulas, I say (to myself) IF_this, then this, otherwise this

 

[dbsbender]

Hi – happy Monday – trying to set up a custom number format. This is what I have so far, but I’m stuck. Can anyone help?

[>=1000000] $#,##0.0,,"MM";[>0] $#,##0.0,"K";[Red]($#,##0)"K"

This is the result I’m trying for:


$1,600,000 = $1.6MM
$13,500 = $13.5K
-$1,600,000 = ($1.6MM) - negatives should be red
-$13,500 = ($13.5K) - this shoul - this

 

[Data Savvy]

This formula should solve display issue, but it converts number to text:

=IF(OR(CELLREF>=1000000,CELLREF<=-1000000),TEXT(CELLREF,"#,##0.0,,")&"m",TEXT(CELLREF,"#,##0,")&"k")

 

[cip888]

Opps!!
Need to change a few things to the above. The format for Style2 should be

[<= -1000000]-$#,##0.0,,"M";[<0]-$#,##0.0,"K";General

Also, the code line

Case Is > -1000000

should be

Case Is > 0

Sorry about that!!

lenze

hi Lenze

i have tried your macro, it works great but only if you input number in the tab....would you or anyone else able to tweak that macro , so that it apply style1 to positive number and style2 to negative, even if the number comes from from a formula? Also I would not necessarily needed to be applied to the whole tab, just to a range of cell (ah2:ah18)

thanks

 

[Chrisciuffo]

the positive part of the format is formatted for Millions. The negative part for Thousands. You can't have both mixed like that.

also, note what you are doing here... two commas before "M" and one comma before K. That's correct for for thousands but you have to remain consistent.
[>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General

There is no magic to "M" & "K" They are merely textual references unrelated to real values.

This works great if you want just the number in a cell by itself. What if you wanted to add something to that number, or concatenate using "&" with another cell? I cant seem to find a fix for that.

For example, if I wanted to make a cell say "4.5M Purchase" - it automatically defaults to the underlying number and looks like "4500000 Purchase"

Would greatly appreciate any help!

 

[aehetag]

This works great if you want just the number in a cell by itself. What if you wanted to add something to that number, or concatenate using "&" with another cell? I cant seem to find a fix for that.

For example, if I wanted to make a cell say "4.5M Purchase" - it automatically defaults to the underlying number and looks like "4500000 Purchase"

Would greatly appreciate any help!

Just use TEXT formula with same format for concatenations:

=TEXT(CELLREF,"[>=1000000] $#,##0.0,,""M"";[<1000000] $#,##0.0,""K"";General")&" Purchase"

 

[tehxii7]

Hi,

I'm trying to come up with a custom format with 3 rules that relates to this discussion. I want it to show positive, negative, 0 regardless of how large or small the number is. In M's, K's, or 0 as -. I can't get the three rules to work. Any help?
This doesn't work: [>0]#.0,,"M";[<0](#.0,,"M");[=0]-

Positive and 0: #.0,,"M";[=0]-
Positive and Negative: #.0,,"M";[<0](#.0,,"M")

I assume the final results will work for K's, M's, etc I'm just a bit new to custom formats.

 

[jkinnick]

Hi, I would like to make a custom numbering format for use in a chart that shows the category name first, then total dollar amount in thousands k or millions M (depending on amount) with 2 decimals, then the percentage of the total represented as a % with one decimal. Can someone please show me how to build this?

 

[philmaund]

Just a quick addition: The commas above represent your thousands separator. If yours is different then you need to change it. I use a space as mine so the format changes to "# ##0 "M" or "# ##0 "K"