Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
J. J. is offline
external usenet poster
 
Posts: 6
Default Custom format applicable to negative and positive numbers?

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 747
Default Custom format applicable to negative and positive numbers?

try this

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



On Nov 12, 12:50*am, J. wrote:
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.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default Custom format applicable to negative and positive numbers?

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


"muddan madhu" wrote:

try this

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



On Nov 12, 12:50 am, J. wrote:
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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Custom format applicable to negative and positive numbers?

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.


"Sheeloo" wrote:

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


"muddan madhu" wrote:

try this

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



On Nov 12, 12:50 am, J. wrote:
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.



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default Custom format applicable to negative and positive numbers?

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..

"J." wrote:

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.


"Sheeloo" wrote:

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


"muddan madhu" wrote:

try this

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



On Nov 12, 12:50 am, J. wrote:
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.




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Custom format applicable to negative and positive numbers?

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


"Sheeloo" wrote:

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..

"J." wrote:

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.


"Sheeloo" wrote:

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


"muddan madhu" wrote:

try this

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



On Nov 12, 12:50 am, J. wrote:
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel, change column of negative numbers to positive numbers? Nita New Users to Excel 3 November 27th 07 05:54 AM
Excel 2002 : Convert Positive Numbers to Negative Numbers ? Mr. Low Excel Discussion (Misc queries) 2 November 6th 06 04:30 PM
Custom number format codes for negative numbers IJ Excel Discussion (Misc queries) 5 September 7th 06 10:54 PM
change 2000 cells (negative numbers) into positive numbers lisbern Excel Worksheet Functions 2 August 16th 06 06:54 PM
convert negative numbers to positive numbers and vice versa bill gras Excel Worksheet Functions 4 December 7th 05 02:39 AM


All times are GMT +1. The time now is 05:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"