Absolute Color Coding - Excel 2007

[dnegrotto]

if an cell A1-A10 you had 10 different correlations and you wanted to conditionally format them so that the ones which has the closest absolute value to 0 are green while the ones gradually farther away from that are red (like a heat sensor) how would you do that....also alternatively....if you placed the absolute values of those correlations in cell b1-b10....is there a way to conditional format cells a1-a10 so that it gives the same heat sensor for the absolute values in cells b1-b10.


My purpose is those correlations are dynamic and always are changing and I want to make it so that I can see visual the green correlations which have an absolute value closest to zero are green while the bad ones are red (farther away from zero).

 

[Hui]

In Excel 2010 you can use Color Scales

Select your range

Goto the Conditional Formatting menu and select Color Scales

select the appropriate color scale


Can anybody confirm if there available in Excel 2007 ?

 

[dnegrotto]

Hi Hui, yes no I know how to do that but here is the problem, The cells I want to heat code i don't want to heat code by their actualy value I want to heat code them by their absolute value


So when I run a correlation analysis and I have -0.06 as a correlation, I'd want to heat code it so that it actually codes the numbers of how close they are to 0...the closer to zero the better, but the ones in which you do teh color range will code -0.06 as worse or better because it doesnt look at the number as an ABSOLUTE value, it instead sees it as -0.06.

 

[Radu]

Hi,


Assuming you have the values in the range A1:A10, select the cells you want to format, go to Conditional Formatting-Color Scales-More Rules. Make sure you've highlighted the

"Format all cells based on their values" option. In the "edit the rule description" box:

- if you want to highlight cells based on a 2-color scale, on the "Type" field, choose Formula, then enter =MIN(0-ABS($A$1:$A$10)) -- notice the absolute references, Excel will not let you use relative references on this one ;

- if you want to format cells based on a 3-color scale, enter the formula =MIN(0-ABS($A$1:$A$10)) in the "Midpoint" section, and leave the other fields ("Minimum" and "Maximum") as they are.


Hope this helps,

Radu

 

[Hui]

Dnegrotto


In Excel 2007 & 2010 you can add as many CF as you like

In Excel up to 2003 you canh only use 3 CF's


Add as many CF's as you need to define your heart map


eg:


select A1:A10

add the following CF's starting with the largest number first


=$B1<0.25 Format Blue, Stop if True Yes

=$B1<0.5 Format Green, Stop if True Yes

=$B1<0.75 Format Yellow, Stop if True Yes

=$B1<0.9 Format Orange, Stop if True Yes

=$B1<1.0 Format Red, Stop if True No


Cut back to 3 CF's in Excel upto 2003

 

[dnegrotto]

guys thanks so much


I ended up doing what radu said but slightly changed I did min(abs(a1-a5) and colored it green and then did max(abs(a1-a5) and colored it red and had the 50 percentile be yellow so that all the numbers grouped closest to zero are green, while the mid way ones are yellow and the highest ones are red. I really appreciate all your help everyone as I was stuck on this for quite sometime