Custom format applicable to negative and positive numbers?

[J.]

I currently have a custom format that shows a number over 1000 as a $1K -
$999K and over 1,000,000 as $1M, etc. This works for positive numbers but I
can not figure out how to also have it work for negative numbers. The format
I currently use is the following:

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

Any suggestions about how to have this same formatting apply to negative
numbers so that they show up as -$1K, -$1M, etc.?

Thanks.

 

[muddan madhu]

try this

[<-100000]-$0,,"M";[<=-1000]-$0,"K";$0

 

[Sheeloo]

I think J wants the formatting for both positive and negative numbers..

I believe only 3 conditions can be applied at a time so it may not be
possible just using formatting...

What J can do is to have formatting for positive numbers as
[>=1000000]$0,,"M";[>=1000]$0,"K";$0

and use conditional formatting for negative numbers
Choose Cell value as between 0 and -99999999999999999999
and formatting as
[<-100000]-$0,,"M";[<=-1000]-$0,"K";$0

try this

[<-100000]-$0,,"M";[<=-1000]-$0,"K";$0

I currently have a custom format that shows a number over 1000 as a $1K -
$999K and over 1,000,000 as $1M, etc. This works for positive numbers but I
can not figure out how to also have it work for negative numbers. The format
I currently use is the following:

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

Any suggestions about how to have this same formatting apply to negative
numbers so that they show up as -$1K, -$1M, etc.?

Thanks.

 

[J.]

Thank you both. Yes, I am trying to apply the formatting to a single cell
which could be negative or positive based on calculations. Sorry that I did
not make that explicit. Mudan's suggestion would would just cover the
negative values like my original just covers the positive. I don't know how
to implement Sheeloo's suggestion in conditional formatting. How can I
apply the same type of custom formatting in the conditional formatting dialox
box? All I seem to be able to do is adjust font, border and patterns. What
am I missing? I am using Excel 2003.

Thanks.

I think J wants the formatting for both positive and negative numbers..

I believe only 3 conditions can be applied at a time so it may not be
possible just using formatting...

What J can do is to have formatting for positive numbers as
[>=1000000]$0,,"M";[>=1000]$0,"K";$0

and use conditional formatting for negative numbers
Choose Cell value as between 0 and -99999999999999999999
and formatting as
[<-100000]-$0,,"M";[<=-1000]-$0,"K";$0

try this

[<-100000]-$0,,"M";[<=-1000]-$0,"K";$0

I currently have a custom format that shows a number over 1000 as a $1K -
$999K and over 1,000,000 as $1M, etc. This works for positive numbers but I
can not figure out how to also have it work for negative numbers. The format
I currently use is the following:

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

Any suggestions about how to have this same formatting apply to negative
numbers so that they show up as -$1K, -$1M, etc.?

Thanks.

 

[Sheeloo]

Sorry, I do not have access to Excel 2003...

In Excel 2007 I get four tabs for Formatting under Conditional Formatting...
Number, Font, Border and Fill

I believe same options were there in Excel 2003 also but I may be wrong..

Thank you both. Yes, I am trying to apply the formatting to a single cell
which could be negative or positive based on calculations. Sorry that I did
not make that explicit. Mudan's suggestion would would just cover the
negative values like my original just covers the positive. I don't know how
to implement Sheeloo's suggestion in conditional formatting. How can I
apply the same type of custom formatting in the conditional formatting dialox
box? All I seem to be able to do is adjust font, border and patterns. What
am I missing? I am using Excel 2003.

Thanks.

I think J wants the formatting for both positive and negative numbers..

I believe only 3 conditions can be applied at a time so it may not be
possible just using formatting...

What J can do is to have formatting for positive numbers as
[>=1000000]$0,,"M";[>=1000]$0,"K";$0

and use conditional formatting for negative numbers
Choose Cell value as between 0 and -99999999999999999999
and formatting as
[<-100000]-$0,,"M";[<=-1000]-$0,"K";$0

try this

[<-100000]-$0,,"M";[<=-1000]-$0,"K";$0



I currently have a custom format that shows a number over 1000 as a $1K -
$999K and over 1,000,000 as $1M, etc. This works for positive numbers but I
can not figure out how to also have it work for negative numbers. The format
I currently use is the following:

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

Any suggestions about how to have this same formatting apply to negative
numbers so that they show up as -$1K, -$1M, etc.?

Thanks.

 

[J.]

That explains it. No, not available in 2003. Well, good to know this is
addressed in 2007. Thank you anyway.

Sorry, I do not have access to Excel 2003...

In Excel 2007 I get four tabs for Formatting under Conditional Formatting...
Number, Font, Border and Fill

I believe same options were there in Excel 2003 also but I may be wrong..

Thank you both. Yes, I am trying to apply the formatting to a single cell
which could be negative or positive based on calculations. Sorry that I did
not make that explicit. Mudan's suggestion would would just cover the
negative values like my original just covers the positive. I don't know how
to implement Sheeloo's suggestion in conditional formatting. How can I
apply the same type of custom formatting in the conditional formatting dialox
box? All I seem to be able to do is adjust font, border and patterns. What
am I missing? I am using Excel 2003.

Thanks.

I think J wants the formatting for both positive and negative numbers..

I believe only 3 conditions can be applied at a time so it may not be
possible just using formatting...

What J can do is to have formatting for positive numbers as
[>=1000000]$0,,"M";[>=1000]$0,"K";$0

and use conditional formatting for negative numbers
Choose Cell value as between 0 and -99999999999999999999
and formatting as
[<-100000]-$0,,"M";[<=-1000]-$0,"K";$0


:

try this

[<-100000]-$0,,"M";[<=-1000]-$0,"K";$0



I currently have a custom format that shows a number over 1000 as a $1K -
$999K and over 1,000,000 as $1M, etc. This works for positive numbers but I
can not figure out how to also have it work for negative numbers. The format
I currently use is the following:

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

Any suggestions about how to have this same formatting apply to negative
numbers so that they show up as -$1K, -$1M, etc.?

Thanks.