Office 2010 Pro Fun With Conditional Formatting

Yesterday I wrote about some work I was doing with Tables recently. Today I want to do the same for conditional formatting – specifically, using colour scales. (For a refresher, or for those that are new to this blog, you can read up on changes to conditional formatting in this series of blog posts, and you can read specifically about colour scales here).
In this case, I was looking at a table that contained the results of a set of tests. The table looked like this (again, I made up the data for this blog entry).
(Click to enlarge)
Essentially, we run a bunch of scenarios against a series of tests periodically and look at the results. 0 is ok, positive numbers are good (the higher the better), and negative numbers are bad (lower means worse). Seemed like an ideal candidate for a colour scale. Using a button that I had added to my Quick Access Toolbar, I applied a colour scale to the table. Here is the result.
(Click to enlarge)
As you can see,Office 2007 Serial, there are a couple of problems. The biggest one is that there are obviously some outliers on the low end of the scale that mean that everything else looks good (remember, that in the default application, the 3-colour scale uses the lowest point, the 50 percent mark, and the highest value to assign colours … if there is an outlier on either end of the data, it can lead to results like this). You can really see the problem if you click on the image above. So, to fix that,Windows 7 Home Premium, I made some adjustments to the rule. Using the manage rules dialog …
(Click to enlarge)
… I made a couple of changes to how the rule was going to be evaluated. First, I changed the midpoint from 50% to a number,Office Ultimate 2007 Key, and set that number to 0 (since above 0 was good and below was bad). Second, I used a formula to determine the value for the minimum (lower bounds) colour to eliminate the effect of the outliers. Specifically, I changed the minimum to be of type “formula” and entered this formula, which simply determines what the value is of the 5th percentile: =PERCENTILE($C$3:$I$52, 0.05). (Choosing the 5th percentile is not terribly scientific, but I had seen this report enough to know that there were never more than one or two outliers, so using the 5th percentile should handle them).
(Click to enlarge)
So now my rule ran something like this – “turn -1.36 (what the formula calcs to in this data set) and everything below it deep red. Everything between -1.36 and 0 is a gradient fill between red and yellow. Everything between 0 and the highest value is a gradient fill between yellow and green. The results look something like this:
(Click to enlarge)
That was better,Microsoft Office 2007 Enterprise, but still not what I wanted, since the effect was pretty busy. So, I decided to make one more change. Once again I opened up the Edit Rule dialog, and this time I changed the colour for the midpoint from yellow to white.
(Click to enlarge)
Once I hit OK a couple of times, the result looked like this …
(Click to enlarge)
… which is pretty much what I wanted. Values close to 0 are mostly white, so they are not called out as good or bad visually, the outliers on the negative side (probably measurement problems) are not skewing the results,Office 2010 Pro, and there is good gradation on both sides, allowing me to quickly eyeball around for things to investigate.
Hopefully that was interesting – the point being that by tweaking the rules themselves, there is no end of possibilities available with conditional formatting.
<div