Conditional formatting for heat map

[madelinemsharpe]

I am trying to set up a heatmap in excel 2007 using conditional formatting. I need all ABS(2.5) and above to be red, all values between ABS(2.4) and ABS(.99) to be yellow and all values less than ABS(1) to be green. I cannot figure out the right equation for my middle criteria (yellow) and also I cannot get excel to recognize the negative numbers. For example it keeps showing all the negative numbers as green.

Please help!

Thank you,

Maddie

 

[Weazel]

so I'm a little confused on your 'green'. If you want everything less then 1 green it would conflict with the lower end of 'yellow'.
so maybe everything less than .99 ?

you should select the range with the values before entering the conditional formatting formulas, just kinda makes it easier

Red =ABS(A2)>=2.5

yellow =AND(ABS(A2)<2.5,ABS(A2)>0.99)

Green =ABS(A2)<.99

 

[madelinemsharpe]

so I'm a little confused on your 'green'. If you want everything less then 1 green it would conflict with the lower end of 'yellow'.
so maybe everything less than .99 ?

you should select the range with the values before entering the conditional formatting formulas, just kinda makes it easier

Red =ABS(A2)>=2.5

yellow =AND(ABS(A2)<2.5,ABS(A2)>0.99)

Green =ABS(A2)<.99

Hi Weazel - Yes I mistyped the green parameter. It should be .99. I typed in what I thought was your forumulas exactly but it is still not working. These were the sample numbers I used. ALl the positive numbers turned green and all the negative numbers turned red.

	March	June	Sept	Dec
Dow	0.99	3.6	1.4	2.1
Vix	-0.7	-2.5	-6.2	4

<COLGROUP><COL style="WIDTH: 54pt" span=5 width=72><TBODY>
</TBODY>

 

[Weazel]

ok,

maybe I'm just confused, but maybe try it like this.

where B4 is green, B5 is yellow, and B6 is Red

Excel 2012

	A	B	C	D	E
1		March	June	Sept	Dec
2	Dow	0.99	3.6	1.4	2.1
3	Vix	-0.7	-2.5	-6.2	4
4		FALSE
5		TRUE
6		FALSE

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B4 =ABS(B2)<0.99 B5 =AND(ABS(B2)>=0.99,ABS(B2)<2.5) B6 =ABS(B2)>=2.5 <tbody> </tbody>

<tbody>
</tbody>

 

[madelinemsharpe]

ok,

maybe I'm just confused, but maybe try it like this.

where B4 is green, B5 is yellow, and B6 is Red

Excel 2012

	A	B	C	D	E
1		March	June	Sept	Dec
2	Dow	0.99	3.6	1.4	2.1
3	Vix	-0.7	-2.5	-6.2	4
4		FALSE
5		TRUE
6		FALSE

<TBODY>
</TBODY>

Sheet2​

Worksheet Formulas Cell Formula B4 =ABS(B2)<0.99 B5 =AND(ABS(B2)>=0.99,ABS(B2)<2.5) B6 =ABS(B2)>=2.5 <TBODY> </TBODY>

<TBODY>
</TBODY>

When I do this it only works on the cell that is in the forumulas. What am I doing wrong?

 

[Weazel]

you would need to select b2:e3 first, then apply the formatting

 

[madelinemsharpe]

you would need to select b2:e3 first, then apply the formatting

when i select b2:e3 like so, ABS(b2:e3), it returns #value! to the B4 -B6 cells and the formatting doesn't work at all.

 

[Weazel]

you would need to select b2:b3 first.

after its selected enter the formulas in conditional formatting just the way they are

 

[madelinemsharpe]

you would need to select b2:b3 first.

after its selected enter the formulas in conditional formatting just the way they are

Still doesn't work. Here's what I'm doing:

A	B	C	D	E
	mar	Jun	Sep	Dec
DOw	.99	3.6	1.4	2.1
Vix	-.07	-.25	-6.2	4
	True	'=ABS(B2)<0.99
	False	'=AND(ABS(B2)>=0.99,ABS(B2)<2.5)
	False	'=ABS(B2)>=2.5

<TBODY>
</TBODY>

I entered the above information in excel - the B rows 5-7 hold the equations that I typed in column C. I select B3:E4. I then click on conditional formatting and select New Rule. I picked "format all cells based on their values". And I changed format stype to 3-color scale. In the minium, midpoint and max boxes I change the type to "formulas" I then put = B5 in the value for min, =B6 in the value for mid, and =B7 in the value for max. When I do this B3:E:3 turn green, along with E:4 and C4:D4 turn red.

What in the world am I doing wrong?!?

 

[Weazel]

Ok, I think I understand...instead of using 3 color scale go to conditional formatting ... new rule....use a formula to determine which cells to format....then in the format values where this formula is true paste in the first formula click on format and select the color for the first rule.

you would repeat this process for the 3 rules, selecting your desired color.

remember to select the range prior to creating the conditional format if it doesn't stay selectled