Multiple Conditional Formatting

[AbrahamGluck]

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD="align: center"]Symbol[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Strike[/TD]
[TD="align: center"]Type[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]$95.00[/TD]
[TD]$85.00[/TD]
[TD]Put[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]$95.00[/TD]
[TD]$85.00[/TD]
[TD]Call[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[AbrahamGluck]

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

Yes, I agree really sharp people! excel is a great skill and always a handy tool to use. Do you have a recommendation how to learn that? Or just practical experience?

 

[Eric W]

The LOOKUP part of the formulas works because if you give it a number bigger than anything you expect to find in your range, it just returns the last number in the range, ignoring blank lines. So I arranged the range to go from row 2 to the current row, and it grabs the last value, which should match your stock code. (Some people prefer 9.999999999999999E+307, but that's very long, and 2^99 is still far above any dollar amount you might have.)

As far as learning Excel, it pretty much depends on your personal learning style. My path was mostly practical experience; a lot of practice and digging through manuals. There are some good books, John Walkenbach, Mr. Excel on this forum, the Dummies guides. Just dig in and practice a lot, try new things.

I'm glad I could help!

 

[AbrahamGluck]

https://1drv.ms/x/s!AowvE26EV4I9jzQlSxnMfuEGkA9e




Hi Eric,
i'm not sure why its happens to be not working for me,
so i shared an example of my sheet and see if you can fix the conditional formating for me in column E
Thanks a lot