Justification for color gradient changes in Conditional Formatting

[AitchK]

I've successfully implemented a heat map for my boss (who is thrilled), but he asks me, 'how do you determine the tipping point from one color to the other'. My lame answer was: "uh, probably based on lowest and highest value, some averaging, and application of pixel density based on that".
What's the formula behind the color densities? Is it possible to see that? Someone asked this same question on mrExcel without any good answer.
http://www.mrexcel.com/forum/excel-...ew-formula-behind-conditional-formatting.html

 

[Luke M]

Hi Aitch.

First, it can matter based on whether you have you CF set to use Percent or Percentile. Think of it as the difference between using the Mean or the Median. I've tried to illustrate some of the changes in the attached workbook. To answer your boss's question, the short answer is that it depends on how close the number is to one of the triggers (high point, low point, and sometimes middle), and then calculates what percentage of that color should be used in the gradient.

 

[AitchK]

Luke, thank you for you Ninja quality response. I downloaded the file you provided, and finally understood how to manipulate the basis of the color changes. My boss and I have been playing around with this lately and I appreciate your help making me better aware of the CF interface. Happy Holidays 2014!