Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I am trying to use conditional formattingas follows:
    if a % is between 0 and + or - 8.99% record a green dot icon;
    if the % is between 9% and 19.99% or -9% and -19.99% record a yellow dot;
    if the % is greater than 20% or less than -20.0% record a red dot;

  2. #2
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Sorry, I pulled the trigger too fast. to the above I am attaching a worksheet to demonstrate.

    The new conditional formatting looks awesome.

    Thank you
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    LOL - well, I can do the conditional formatting easily enough - but what I can't figure out is how to keep the cells being referred to as a relative link instead of an absolute one! That is, I write the formulae for the first cell, then before you used to just copy it to the rest and the cells referred to would update along with it, same as ordinary cells do. Eg "E2=1" in the first cell, copied down one cell would become "E4=1" - but now XL just makes it absolute and refers to a range to apply the formatting to instead.

    I presume the answer is relative cell addressing (R1C-1 or similar) but I don't know and haven't been able to work out how to use this ...

    Edited: PS I'm presuming here that what MNN wants is to colour the words 'Green', 'Yellow' and 'Red' in his sample spreadsheet, according to his formulae - he can then change the text to a dot as required ...
    Beryl M


  4. #4
    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
    Use <F4> to toggle through the cells to change from absolute to relative ($A$1, $A1,A$1,A1). The conditional formatting uses those references in the cells. The formula references are all based on the activecell of the selected range.

    Steve


  5. #5
    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
    If you add a formula to that column using ABS to convert the absolute percentage values, you can then use icon sets and hide the formulas. See attached.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Rory came up with a winner. The formula making the variance % an absolute is key. However, What happens to the result? Only the conditional formatting icon is shown. I thought both the conditional formatting icon and the formula result would be demonstrated. Why does this happen in this conditional formatting situation.

    Thanks again.

  7. #7
    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
    I specifically hid the result using a cell format of ;;;
    If you want to show it, simply reset the cell format to Percentage.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Neat trick. Thank you again.

Posting Permissions

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