Number format M and K

[jleigh37]

Hello,

The numbers in my Excel spreadsheet are already formatted in thousands. Example, 5,010,000 is displayed as 5,010.

I am linking it to a summary in Word, but need the numbers to be formatted to $5.0M (or $#.#M) both in positive and (negative) numbers, no red.

I also need to add numbers that are in thousands to display with a "K" but only in 3-digit integers with parentheses for negatives, such as 627 as $627K or ($627K), no decimals.

So far I have:

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

and

[>=1000] ($#,###.0),"M";[<1000]($###"K");General

The second one is displaying ($627K) as -($627K)...I can't seem to get rid of the pesky "-". But it doesn't work with my $5.0M number and vice versa with the first custom format. I can get $5.0M but not $627K. I'm also not sure how to add parentheses to each if they are negative.

Any help would be much appreciated.

 

[pgc01]

Hi

I'd use the number format:

0;(0)

This takes care of small numbers (-1000,1000)

Then add one conditional formatting rule for big positive numbers

=A1>=1000

with the number format:

[>=1000000]0.0,,"M";[>=1000]0,"K"

and a second conditional formatting rule for big negative numbers

=A1<=-1000

with the number format:

[<=-1000000](0.0,,)"M";[<=-1000](0,)K

 

[pgc01]

P. S.

In my example I was formatting A1

 

[jleigh37]

Thanks pgc01. I tried your way and wasn't able to figure it out, but I did play around with it and created this:

[>=1000]$#,###.0,"M";($#"K");$#"K"

Seems to work for now. Thanks for the response though!!