Color-coded based on position in a chart

[#1]

Hello, I have a simple chart that lists project names and chances of success (as percentages) in two columns.  The project name is the dimension.  What I need to do is to color the background of the chance of success cells by the following cirteria:

If the chance of success rating for that project is in the top 10% of chance of success ratings for the projects in the selection, color background green

If the chance of success rating for that project is in the bottom 15% of chance of success ratings for the projects in the selection, color background red.

This means that, if there are 20 projects in the project list, only the top two chance of success valued cells should be green and the bottom three valued cells red.  It doesn't matter what the actual values of the chance of success are - it only matters where the values are with respect to the other values for the projects in the set.

I've tried various approaches with inter-record functions and range functions but I can't find a workable approach.  Can someone help me create this logic?

[swuehl · Accepted solution]

You can use expression attributes to set the background color. On expression tab, open the attributes of your expression by clicking on the small + sign next to the expression label in the upper left box.

For background color, use a definition like

=if(rank(Chance) <= count(total Project)*0.1,LightGreen(),

if(rank(-Chance) <= count(total Project)*0.15,lightred()

))

See also attached.

Stefan

[#3]

This was exactly what I needed - thank you!