Multiple Conditional Formatting

[AbrahamGluck]

Symbol	Price	Strike	Type
AAPL	$95.00	$85.00	Put
AAPL	$95.00	$85.00	Call

<tbody>
</tbody>

What I want to do is a conditional formating the column of Strikes, that needs to met 2 criterias,
if the type is a "call" then if is below the stock price should be highlighted,
and if a put is resulting for the type then what's above the stock price should be highlighting

Thank you

 

[63falcondude]

Highlight the Strike values (in your example, that will be C2:C3). Select Conditional Formatting, New Rule, use a formula, =AND(D2="Call",C2 < B2) format how you'd like.<b2) format="" how="" you'd="" like.<="" html=""></b2)>

 

[AbrahamGluck]

This will probably work nice for the Call types, will this work for the puts type their is greater then the stock price?

 

[63falcondude]

Yes, that is incorporated into the formula. C2 < B2

 

[Eric W]

Highlight the Strike column, which I assume is C and use this formula:
=XOR($D1="Put",$C1<$B1)

It should work for both calls and puts, if those are the only 2 values in column D.

63falcondude, your formula with the AND will never highlight a Put. You could add a second rule:
=AND(D2="Put",C2 > B2)

but the XOR handles both cases.

 

[AbrahamGluck]

Highlight the Strike column, which I assume is C and use this formula:
=XOR($D1="Put",$C1<$B1)

It should work for both calls and puts, if those are the only 2 values in column D.

63falcondude, your formula with the AND will never highlight a Put. You could add a second rule:
=AND(D2="Put",C2 > B2)

but the XOR handles both cases.

Thank you! this is exactly what I want and works for puts and calls, (cell $B$1 i changed from being $B1, for my purpose of use)

to make it even better I would like to know if you can do this with a lookup function (and still use it in the conditional formating) =XOR($D1="Put",$C1<$B$1) instead of just formulating $C1<$B1 look it up the symbol and find the price, the lookup value is not all the time the same cell, but its will always be the first value that find in column A from the row we are staying to the way up, let say we're in cell C7 the value to lookup will be in cell A7 and if nothing there then the next place is A6 all the way up

AAPL	95.00
GOOG	77.00
AMZN	66.00
SPY	55.00
QQQ	44.00
DIA	100.00
GLD	23.00
USO	11.00
TLT	144.00
MSFT	32.00

<tbody>
</tbody>

 

[Eric W]

I'm not entirely sure what your table looks like.

If it looks like this:

	A	B	C	D
1	Symbol	Price	Strike	Type
2	AAPL	$95.00	$85.00	Put
3			$85.00	Call
4	GOOG	77	55	Put
5			44	Put
6			88	Put
7			99	Call
8	AMZN	66	60	Call
9			62	Call
10			70	Call
11

<tbody>
</tbody>

Sheet1​

you can select range C2:C999 (or whatever), and use this formula:
=XOR(D2="Put",C2< LOOKUP(2^99,B$2:B2))*(C2<>"")

 

[63falcondude]

63falcondude, your formula with the AND will never highlight a Put. You could add a second rule:
=AND(D2="Put",C2 > B2)

Looking back, I must have only read the first half of the question... Whoops.

 

[Eric W]

Another set of eyes is often very handy. It's nice that there's a lot of sharp people here.

 

[AbrahamGluck]

I'm not entirely sure what your table looks like.

If it looks like this:

	A	B	C	D
1	Symbol	Price	Strike	Type
2	AAPL	$95.00	$85.00	Put
3			$85.00	Call
4	GOOG	77	55	Put
5			44	Put
6			88	Put
7			99	Call
8	AMZN	66	60	Call
9			62	Call
10			70	Call
11

<tbody>
</tbody>

Sheet1​

you can select range C2:C999 (or whatever), and use this formula:
=XOR(D2="Put",C2< LOOKUP(2^99,B$2:B2))*(C2<>"")

Thanks Eric!
really amazing how this workout, I have no idea how this works, it's very interesting. Cool.