Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Kent, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have some conditional formatting set up and it seems that no matter what order I put things in, it doesn't quite work.

    Please see the attachment which shows 96% formatted in Orange, when the conditional formatting seems to state it should be red.

    I hope someone can help

    Thanks

    Emily
    Attached Images Attached Images

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your formulas don't make sense as & is a concatenation operator - i.e. it joins two bits of text together. Perhaps you could post a sample workbook and clarify exactly what you want the formatting to be.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Kent, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for taking an interest Rory.

    I would like it to be green if it is between 81% and 89%

    Amber if it between 75% and 80% or 90% and 95%

    Red if its less than 75% or greater than 95%.

    Thanks in advance

    Emily

    P.S.
    It seems there is a bit of an error in the formulas in the attachment in that for red it says less tha 60% or more than 95%. This still doesn't account for 96% showing as amber (I think).

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your second formula would be:
    =OR(AND(P93>0.75,P93<=0.8),and(P93>0.9,P93<=0.95))

    You don't really need the third condition since it's just an 'otherwise' condition - just format your cells with a red font. The CF formats will either override it or not as required.

    One additional thing - I would alter your first condition to also use a formula otherwise you leave gaps between 80% and 81% and 89% and 90%:
    =AND(P93>0.8,P93<0.9)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Oct 2002
    Location
    Kent, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Rory

    That worked a treat.

    Emily

Posting Permissions

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