Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Conditional formatting with five-icon set

    Hi All,

    I am fighting with Excel 2007's way to conditionally format cells B4 through K4 using a 5-Icon set of conditions. No matter what conditions I specify it always displays the negative value with a green arrow instead red.

    The attached workbook shows my last try.

    Any help is greatly appreciated.

    Best regards,

    Wolfgang
    Attached Files Attached Files

  2. #2
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,194
    Thanks
    44
    Thanked 226 Times in 210 Posts
    Wolfgang

    Try changing conditional type from percent to number leaving the cell formatting as percent. Change the values to decimels as shown. Because I do not have the linked workbook, I manually entered different values into the cell (ex .75 or 60%) and the icons changed appropriately.
    Icon2.jpg Icon3.jpg

    HTH,
    Maud
    Last edited by Maudibe; 2013-03-24 at 09:44.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Maude,

    thank you very much for your Sunday answer!

    I applied your suggestions and they seem to work as long as all values stay negative. But as soon as they turn positive the red arrow stays. The possible %-range may be anything between a minus 50 and a plus 50.

    Some more beautiful Sunday ideas, maybe?

    Best,
    Wolfgang

  4. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,194
    Thanks
    44
    Thanked 226 Times in 210 Posts
    Wolfgang,

    In what format are the values coming from the linked workbook?
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    What value do you want to transition for each of the arrows?

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Maude and Steve:

    @Maude: This is my famous Stock-Tracking-Import file which imports all values "as is" meaning, Standard-Values which I format later on my Monitor-sheet accordingly (all values as Numbers or Time values or Date values and Percentages as %).

    @Steve: Since I am mainly interested in %-changes I like to monitor the changes regrading their increasing or deceasing behavior. So any changes within a 10% margin should do.

    Thank you both..

    Wolfgang

  7. #7
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Maude,

    this is my Web-import file which I process...
    Attached Files Attached Files

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Since I am mainly interested in %-changes I like to monitor the changes regrading their increasing or deceasing behavior. So any changes within a 10% margin should do.
    That doesn't really answer the question. But based on this I would set it as attached. This will be Green arrow for >=20%, yellow up >=10% and <20%, yellow horizontal from >=-10% and <10%, yellow down <-10% and >=-20%. the red arrow will be when the value is <20%.

    [Note: The "type" of percentage is meaningless if based on only 1 cell. It is meant to calculations on a range of cells, and it colors them based on the percentiles of the distributions]

    Steve
    Attached Images Attached Images

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    wolfgang (2013-03-25)

  10. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Good Morining Steve,

    It works as I wanted. Actually I have a range of 25 cells only my example shows 1.

    Thank you very much for your time and help.

    Best regards,

    Wolfgang

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You are very wecome.

    But even though you have a range of cells, in your original CF list, they were all setup individually. Also from your description it seems to me that you want the color based on individual values, not their place in the distribution of all 25 cells.

    The type "number" uses the values regardless of the distribution so the conditions can have positive and negative numbers. The type "percent" bases the icon on the "percentrank" of the number in the distribution (and thus requires only positive entries). In the type percent a change in ANY of the values may change the icon for every one of the conditioned cells even if their values have not changed.

    Steve

  12. #11
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Steve,

    your explanation is highly appreciated!

    Have a nice Easter holiday season.

    Best,

    Wolfgang

Posting Permissions

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