Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatic data entry based on conditional formatti (Excel XP)

    I have a customer that is creating what they call dashboards. We have set up 3 conditional formattings on a cell. In a column to the left we want the word for the pattern that is displayed as a result of the conditional formatting. So if the pattern is yellow for the conditionally formatted cell we want the word "yellow" to appear in the column to the left. I know about the IF statement but that gives you only two choices. I need up to four colors. Does any one have any ideas?

    Thanks Fay

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatic data entry based on conditional formatti (Excel XP)

    You can nest IF up to 7 levels. For example:

    =IF(B4>100,"Red",IF(B4>50,"Orange",IF(B4>0,"Yellow ","Green")))

    Does that help?

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Automatic data entry based on conditional formatti (Excel XP)

    Indeed, for this problem, there's no need to nest the IFs, so you can get the full 1024 chars if needed:
    =IF(B4>100,"Red","")&IF(B4>50,"Orange","")&IF(B4>0 ,"Yellow",")&IF(B4>-50,"Green","") ....
    Only the very last in the chain would need a 'default' colour to be set by the ELSE part of the IF statement.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry based on conditional formatti (Excel XP)

    If I put your formula in a cell (and add the missing double quote in the yellow test) and set B4 to 150 then I get: RedOrangeYellowGreen

    I don't think that is what was wanted. Am I missing something?
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry based on conditional formatti (Excel XP)

    I tried Hans's suggestion and we got Y when R should have been displayed. I then used macropod's formula as given here =IF(D15>99.99%,"G","")&IF(D15<100%,"Y","")&IF(D15< 85%,"R","") it worked like a champ except as noted by Legare. I got YR when the cell was colored red. I can almost live with that, but I bet there is a correction that is possible.

    As always I appreciate your help. I always learn alot in this lounge.

    Fay

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry based on conditional formatti (Excel XP)

    Does

    =IF(D15>=1,"G",IF(D15<0.85,"R","Y"))

    help at all ?

    Andrew C

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry based on conditional formatti (Excel XP)

    You are two for two. Thank you so ever much.

    Have a great Sunday.

    Fay

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatic data entry based on conditional formatti (Excel XP)

    Macropod's method of having more than 7 IF statements, works ONLY if the conditions in each of the concatenations is mutually exclusive. His example was NOT, since ALL the conditions were met if B4 were >100 (thenB4 is ALSO >50, it is > 0 and it is >-50). You want to use something more like this (to modify your example):

    =IF(D15>99.99%,"G","")&IF(AND(D15<100%,D15>=85%)," Y","")&IF(D15<85%,"R","")

    Now they are mutually exclusive. Though this can also be don with the combined IFs as previously posted

    Another technique that I prefer, if you get into that construction and NEED to have more than 7 IFs, is to create a table. Put the limits in one column and the result in the other. Then use something like VLOOKUP to lookup the value in the table and get the result.

    It works like the construction with more than 7 IFs, but it is much easier to modify. Only 1 table to change items in, no need to play with the formulas at all.

    Steve

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry based on conditional formatti (Excel XP)

    I just copied Hans' formula and pasted it into a worksheet and it seems to work like it should for me. Can you be a bit more specific about exactly what formula you used and what values in B4 gave incorrect results?
    Legare Coleman

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Automatic data entry based on conditional formatti (Excel XP)

    Oops!

    Should have been something more like like:
    =IF(AND(B4>100,B4<=50),"Red","")&IF(AND(B4>50,B4<= 0),"Orange","")&IF(AND(B4>0,B4<=-50),"Yellow","")&IF(B4>-50,"Green","Blue")
    Glad you picked that up.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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