Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 13th 08, 08:38 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 293
Default How To Detect A Negative Number In A Cell

Greetings,

I am trying to detect whether the value in cell M for each row has a
negative number. If it does I need to change the text color for that
row to red.

Any help, insights or links would be greatly appreciated.

-Minitman

  #2   Report Post  
Old March 13th 08, 08:54 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,646
Default How To Detect A Negative Number In A Cell

Select column MCell formattingCategory:NumberSelect one of red patterns
for negative numbers
Regards,
Stefi


„Minitman” ezt *rta:

Greetings,

I am trying to detect whether the value in cell M for each row has a
negative number. If it does I need to change the text color for that
row to red.

Any help, insights or links would be greatly appreciated.

-Minitman

  #3   Report Post  
Old March 13th 08, 11:10 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default How To Detect A Negative Number In A Cell

If the OP wants to change the text colour for the whole row, rather than
just for that cell, then it'll need Conditional Formatting.
Formula Is/ =$M1<0
--
David Biddulph

"Stefi" wrote in message
...
Select column MCell formattingCategory:NumberSelect one of red patterns
for negative numbers
Regards,
Stefi


"Minitman" ezt rta:

Greetings,

I am trying to detect whether the value in cell M for each row has a
negative number. If it does I need to change the text color for that
row to red.

Any help, insights or links would be greatly appreciated.

-Minitman



  #4   Report Post  
Old March 13th 08, 01:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 293
Default How To Detect A Negative Number In A Cell

Hey Stefi,

Thanks for the reply.

Since I need the entire row to change, cell formatting won't work. my
problem seems to be in the formula I tried to work in Conditional
Format:

=$M2<0

I can't get this formula to work in a test cell or in CF:

=IF($M2<0,"NEGATIVE","POSITIVE")

I do appreciate the attempt.

-Minitman

On Thu, 13 Mar 2008 01:54:00 -0700, Stefi
wrote:

Select column MCell formattingCategory:NumberSelect one of red patterns
for negative numbers
Regards,
Stefi


Minitman ezt rta:

Greetings,

I am trying to detect whether the value in cell M for each row has a
negative number. If it does I need to change the text color for that
row to red.

Any help, insights or links would be greatly appreciated.

-Minitman


  #5   Report Post  
Old March 13th 08, 01:51 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 293
Default How To Detect A Negative Number In A Cell

Hey David,

Thanks for the reply.

Nice catch on the whole row.

There seems to be a problem with that formula (=$M2<0)

That formula is what I tried at first in CF. I know how to get the
color I need if the formula is working. That formula seems to be the
culprit. So to test the formula, I moved back to the sheet and chose
a column to test the formula. In the first row (row 2), I placed:

=IF($M2<0,"NEGATIVE","POSITIVE")

I then copied it down for 10 rows (it automatically advanced the row
number), I paced both positive and negative numbers (along with a
zero in one row for testing) in the test rows M column.

They all came back "POSITIVE" which is to say they all returned FALSE.

Any idea as to what is wrong with this formula?

-Minitman



On Thu, 13 Mar 2008 11:10:16 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

If the OP wants to change the text colour for the whole row, rather than
just for that cell, then it'll need Conditional Formatting.
Formula Is/ =$M1<0




  #6   Report Post  
Old March 13th 08, 03:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default How To Detect A Negative Number In A Cell

It sounds as if you've got text, rather than numbers, in your column M.
To convert to numbers, try copying a blank cell, then select column M and
use Edit/ Paste Special/ Add.
--
David Biddulph

"Minitman" wrote in message
...
Hey David,

Thanks for the reply.

Nice catch on the whole row.

There seems to be a problem with that formula (=$M2<0)

That formula is what I tried at first in CF. I know how to get the
color I need if the formula is working. That formula seems to be the
culprit. So to test the formula, I moved back to the sheet and chose
a column to test the formula. In the first row (row 2), I placed:

=IF($M2<0,"NEGATIVE","POSITIVE")

I then copied it down for 10 rows (it automatically advanced the row
number), I paced both positive and negative numbers (along with a
zero in one row for testing) in the test rows M column.

They all came back "POSITIVE" which is to say they all returned FALSE.

Any idea as to what is wrong with this formula?

-Minitman



On Thu, 13 Mar 2008 11:10:16 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

If the OP wants to change the text colour for the whole row, rather than
just for that cell, then it'll need Conditional Formatting.
Formula Is/ =$M1<0




  #7   Report Post  
Old March 13th 08, 03:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 293
Default How To Detect A Negative Number In A Cell

Hey David,

Thanks for the reply.

That was the problem.

Thank you very much.

-Minitman



On Thu, 13 Mar 2008 15:11:17 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

It sounds as if you've got text, rather than numbers, in your column M.
To convert to numbers, try copying a blank cell, then select column M and
use Edit/ Paste Special/ Add.


  #8   Report Post  
Old March 13th 08, 04:12 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default How To Detect A Negative Number In A Cell

Glad it worked for you.
--
David Biddulph

"Minitman" wrote in message
...
Hey David,

Thanks for the reply.

That was the problem.

Thank you very much.

-Minitman



On Thu, 13 Mar 2008 15:11:17 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

It sounds as if you've got text, rather than numbers, in your column M.
To convert to numbers, try copying a blank cell, then select column M and
use Edit/ Paste Special/ Add.






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
Negative number morphing to a cell code nickra Excel Worksheet Functions 14 October 1st 07 05:38 AM
cell to show ' 0 ' , or nothing at all, if the sum is a negative number rdmdale Excel Worksheet Functions 4 September 17th 05 03:54 PM
make cell entries a negative number gls858 New Users to Excel 4 August 1st 05 11:00 PM
2003= negative number&2004= negative number How Do I Calculate gro Jason Excel Worksheet Functions 1 January 14th 05 05:24 PM
how to change the state of a number in a cell from negative to po. Steve11 Excel Discussion (Misc queries) 1 November 29th 04 07:00 AM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017