Conditional formating
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Conditional formating

  1. #1
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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?

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  6. #6
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ahhh, thanks now I understand.

  7. #7
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  9. #9
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com