Conditional formating

kojak43

Active Member
Joined
Feb 23, 2002
Messages
270
I thought this was working, but I guess not.
In cell N6 I placed the following under Conditional Formating. If cell value is equal to 1, make the cell red.
I also used a formula on cells A6, B6 and C6 that said formula =If($N6,1) Turn A6,B6 and C6 red as well.
I tested it using a 1 in N6 and it worked fine. If there is no number 1 in N6, none of the cells turn red. That is exactly what I want to happen.
However, I just changed N6 to a 2, and all the cells changed red. (A6:C6 + N6)
What am I doing wrong?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Omit the IF in the conditional formatting box The IF is already assumed. The Formula should be =$N6=1 in A6,B6,C6 etc.
 
Upvote 0
Bless you. It worked perfectly.
If you have a moment, can you teach me what was wrong with the formula I used? My formula was =IF($N6,1) I understod your comment that IF was understood, but reading the formula I placed as a sentence, I think it says, If the value in cell N6 is 1, then turn red. If that is correct, why did a 2 allow the cell to turn red?
 
Upvote 0
Because Conditional Formatting is already an IF statement, Putting IF in the dialog creates a double IF. Your way was being read as IF(IF($N6=1)
 
Upvote 0
On 2002-04-08 12:51, kojak43 wrote:
Bless you. It worked perfectly.
If you have a moment, can you teach me what was wrong with the formula I used? My formula was =IF($N6,1) I understod your comment that IF was understood, but reading the formula I placed as a sentence, I think it says, If the value in cell N6 is 1, then turn red. If that is correct, why did a 2 allow the cell to turn red?

=IF($N6,1) returns 1 for all numbers except for 0. In case N6=0, you get FALSE.

1 (or TRUE) makes A6:C6 turn red, FALSE makes them No Fill (uncolor them).

Aladin
 
Upvote 0
Aladin, maybe I don't understand afterall.

In early March, Chris Davidson sent in a question to Mr Excel. The following is a portion of the formula he sent.
=COUNTIF(A1:A7,5)
If I make this a sentence I think it reads,
In cells A1 to A7, if you see the number 5, count it.

Yesterday, I read your explaination to mean, ",1" represents True, there is a value in cell $N6. It is not an empty cell, so turn the cell red.

So why does the ",5" see a 5 and nothing else, yet the ",1" sees any value and reacts?

I am making one giant assumption. And that is COUNTIF and IF are reasonably the same thing. That is, one counts something if that something is true (",5") and the other does something else if the answer is true.",1". So when I entered a 1, in my formula, the cell, did what I expected and turned red. When I entered a 2, it still turned red. That was unexpected.

Can you straighten me out? I know that can be a daunting task. :wink:
 
Upvote 0
On 2002-04-09 09:39, kojak43 wrote:
Aladin, maybe I don't understand afterall.

In early March, Chris Davidson sent in a question to Mr Excel. The following is a portion of the formula he sent.
=COUNTIF(A1:A7,5)
If I make this a sentence I think it reads,
In cells A1 to A7, if you see the number 5, count it.

Yesterday, I read your explaination to mean, ",1" represents True, there is a value in cell $N6. It is not an empty cell, so turn the cell red.

So why does the ",5" see a 5 and nothing else, yet the ",1" sees any value and reacts?

I am making one giant assumption. And that is COUNTIF and IF are reasonably the same thing. That is, one counts something if that something is true (",5") and the other does something else if the answer is true.",1". So when I entered a 1, in my formula, the cell, did what I expected and turned red. When I entered a 2, it still turned red. That was unexpected.

Can you straighten me out? I know that can be a daunting task. :wink:

=COUNTIF(A1:A7,5)

keeps as it were a counter, initially set to 0. Test each cell to see if its value is equal to 5 (the condition). If so, the counter incremented by 1; if not, the counter keeps its previous value. When the function is finished testing all the cells, it returns the value of the counter.

The IF function in Excel has the following systax:

Condition --> Action1, Action2

where the truth value that results from Condition determines which action occurs:

Condition=TRUE --> Action1

Condition=FALSE --> Action2

Examples of Condtion:

  • A1>=MIN(A1:A5)
    AND(A1=2,A2=5)
    TRUE
    FALSE
    7
    1
    0
    -8

It's just a convention that all numbers except 0, behaves like TRUE, while 0 behaves like FALSE. But, from a computational perspective a very useful one.

Compare:

=IF(TRUE,"Hi","Hello")

=IF(7,"Hi","Hello")

=IF(-1,"Hi","Hello")

and

=IF(FALSE,"Hi","Hello")

=IF(0,"Hi","Hello")

It's a convention that one needs to know about.

Returning to COUNTIF: this has to look for an exact match to a condition, while IF selects an action to execute on the basis of the logical value that its Condition evaluates to.

So, =IF($N6,1) returns 1 if N6>0 or N6<0, otherwise, that is, if N6=0, IF returns FALSE. You get thus Red in case IF carries out the action of computing 1, which is dependent on N6 being greater than or less than 0.

Hope this helps.

Aladin
This message was edited by Aladin Akyurek on 2002-04-09 10:34
 
Upvote 0
Aladin:
You are too kind and it is getting late on your side of the globe, so I am sure you will be pleased to learn I think I am gaining on this.

If I had written:
=IF($N6=1,'Turn a certain cell Red') would that have given me a red cell only when there was a 1 present and not a 2 or 3?

Thanks for your patience.
 
Upvote 0
On 2002-04-09 11:28, kojak43 wrote:
Aladin:
You are too kind and it is getting late on your side of the globe, so I am sure you will be pleased to learn I think I am gaining on this.

If I had written:
=IF($N6=1,'Turn a certain cell Red') would that have given me a red cell only when there was a 1 present and not a 2 or 3?

Thanks for your patience.

No. Because

=IF($N6=1,"Turn a certain cell Red") [ modified ' to "" ]

doesn't return a logical value when $N6=1, only does so when $N6<>1. Conditional Formatting expects a logical value (TRUE or FALSE). If you rewrite it as:

=IF($N6=1,TRUE)

you'll get the desired behavior.

=IF($N6=1,TRUE)

is identical to:

=$N6=1


Aladin
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top