Conditional formating

[kojak43]

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?

[lenze]

Omit the IF in the conditional formatting box The IF is already assumed. The Formula should be =$N6=1 in A6,B6,C6 etc.

[kojak43]

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?

[lenze]

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)

[Aladin Akyurek]

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

[kojak43]

Ahhh, thanks now I understand.

[kojak43]

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.

[Aladin Akyurek]

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.

=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 ]

[kojak43]

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.

[Aladin Akyurek]

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