In between a figure.

[shadow12345]

Morning all,
Can anyone tell me what I need to do in conditional formating to check the below table. If the settlement price is outside of the bid or offer I would like a red box around the bid or offer. (these can be negative or positive numbers).

In the below it should highlight L9 if that helps.
<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>K</b></td><td align="center" bgcolor="#C0C0C0"><b>L</b></td><td align="center" bgcolor="#C0C0C0"><b>M</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>6</b></td><td rowspan="1" colspan="1" width="138" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Bid </font></td><td rowspan="1" colspan="1" width="108" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Offer </font></td><td rowspan="1" colspan="1" width="168" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Settlement Price </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>7</b></td><td rowspan="1" colspan="1" width="138" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-15 </font></td><td rowspan="1" colspan="1" width="108" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-4 </font></td><td rowspan="1" colspan="1" width="168" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-14 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>8</b></td><td rowspan="1" colspan="1" width="138" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-51 </font></td><td rowspan="1" colspan="1" width="108" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-39 </font></td><td rowspan="1" colspan="1" width="168" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-49 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>9</b></td><td rowspan="1" colspan="1" width="138" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-71 </font></td><td rowspan="1" colspan="1" width="108" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-70 </font></td><td rowspan="1" colspan="1" width="168" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-69 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>10</b></td><td rowspan="1" colspan="1" width="138" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-65 </font></td><td rowspan="1" colspan="1" width="108" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">10 </font></td><td rowspan="1" colspan="1" width="168" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-64 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>11</b></td><td rowspan="1" colspan="1" width="138" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-90 </font></td><td rowspan="1" colspan="1" width="108" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-60 </font></td><td rowspan="1" colspan="1" width="168" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-70 </font></td></tr>
</table>

 

[p45cal]

Select M7:M11, making sure M7 is the active cell, then:
<embed src="http://www.box.net/embed/0u49dxz0xzg9smn.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">

 

[shadow12345]

errr that picture isnt showing up on my screen.... what does it say? I assume its the picture of the conditional formating screen

 

[p45cal]

errr that picture isnt showing up on my screen.... What does it say?

?!?!

=or($m7<$k7,$m7>$l7)

 

[shadow12345]

thats almost done it... my fault I forgot to inculde any blanks.

it is possible that either K or L might be blank. Sorry. Could you fix it to inculde that as well?

(p.s on everything else it worked great)

 

[p45cal]

blanks in which column(s)?
if just column M:
=AND(OR($M7<$K7,$M7>$L7),NOT(ISBLANK($M7)))

if all three columns:
=AND(OR($M7<$K7,$M7>$L7),NOT(OR(ISBLANK($M7),ISBLANK($L7),ISBLANK($K7))))

I feel sure they could be shorter.

edit post posting:
you said columns K or L could be blank, so:
=AND(OR($M7<$K7,$M7>$L7),NOT(OR(ISBLANK($L7),ISBLANK($K7))))

 

[shadow12345]

hmmm that doesn't seem to work (the last one you posted). Do you think its because i am using a if and vlookup to check if K or L has a value? if the Vlookup returns a value of more than 2000 i am saying put "" (which I think is blank?)

=IF(VLOOKUP(VALUE(MID(J14,1,7)),$A$7:$G$16,2,FALSE)-VLOOKUP(VALUE(MID(J14,10,7)),$A$7:$G$16,3,FALSE)>2000,"",VLOOKUP(VALUE(MID(J14,1,7)),$A$7:$G$16,2,FALSE)-VLOOKUP(VALUE(MID(J14,10,7)),$A$7:$G$16,3,FALSE))


Or does the formula count as a value?


*i should note, the formula above is in k7

 

[Peter_SSs]

Does this do what you want (apart from I din't use a 'box' )

Excel Workbook
	K	L	M
7	-15	-4	-14
8	-51	-39	-49
9	-20		-40
10	-71	-70	-69
11	-65	10	-64
12	-90	-60	-70
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M7	1. / Formula is =AND(COUNT(K7:M7)=3,M7<>MEDIAN(K7:M7))	Abc

 

[shadow12345]

Well thats done it Peter.... though I dont understand how


Thanks also to p45cal, you solved the first part (teach me to inculde all possible options in my first post)

 

[Peter_SSs]

Well thats done it Peter.... though I dont understand how

There are 2 conditions in the formula. If both are met then the cell is formatted. If either fails then the cell is not formatted.

Cond 1
COUNT(K7:M7)=3
If all 3 cells contain numbers then count K7:M7 returns 3 and this condition is passed. Row 9 in my sample fails this test since COUNT(K9:M9)=2

Cond 2
M7<>MEDIAN(K7:M7)
The median of a set of numbers is the middle number when the numbers are arranged in order. If the settlement price is the median of the 3 numbers then it means the settlement price is between the Bid & Offer prices (or it could be equal to one of them). This condition says that M7 is not the median - as is the case with row 10 in my sample.

Hope that helps.