Excel Functions needed for distinguishing records in a database

[okcsteve]

I need help with the formula in column K in the attached workbook.

It contains a database of companies that were included in an SEC filing submitted by an investment firm. The database contains 522 records in rows 6 – 528 and columns A thru K.

The 522 records are comprised of 336 companies.

Each company is listed either one, two, or three times – depending on whether the investment firm has invested in the Puts, the Calls, or the Stock.

All combinations are included – Puts Only (1 record), Calls Only (1 record), Stock Only (1 record), Puts + Calls (2 records), Puts + Stock (2 records), Calls + Stock (2 records), Puts + Calls + Stock (3 records).

Column G lists whether or not the investment was in the Stock Only (”O” for Only) or a combination (“C”) of the Stock plus either the Puts, Calls, or both. In other words, “C” includes Puts + Stock (2 records), Calls + Stock (2 records), and Puts + Calls + Stock (3 records).

Column H denotes investments in Puts Only, and column I denotes investments in Calls Only.

I need column K to denote (by a two letter symbol “PC”) investments made ONLY in the Puts and/or the Calls – i.e. including the Puts Only (1 record), Calls Only (1 record), or Puts + Calls (2 records).

I tried a formula with AND, OR, and COUNTIF functions – to no avail. Please help!

[FDibbins]

Could you provide some sample answers?
You say your formula doesnt work, where and why is it not working - and what should it have returned?

[okcsteve]

Yes. The values in red in column K denote where my formula is in error. It wrongly marks “PC” when there has been an investment in the Stock plus either a Put or a Call (2 records).

The other values are correct.

[okcsteve]

Also, you also asked why my formula is in error. It is in error because it produces a “PC” when the ticker count is < 3. Well, that test is correctly satisfied when there is an investment in the Stock and only a Put or a Call, but that does not answer what I am going for. So, I know why it is wrong, but I have not been able to work out how to fix it.

[FDibbins]

Yes. The values in red in column K denote where my formula is in error

There are no entries in col K, I think you mean col J?

If so, and using J31 as the 1st example...
=IF(AND(OR(D31="Call",D31="Put"),COUNTIF(A$6:A$528,A31)<3),"PC","")

Breaking that down
=OR(D31="Call",D31="Put") = TRUE
COUNTIF(A$6:A$528,A31)<3) = TRUE (count = 2)
=IF(AND(TRUE,TRUE),"PC","")

Can you explain why you think this is wrong?

[okcsteve]

It is wrong because it denotes a "PC" for an investment in ticker AWI that includes an investment in Stock. I need the formula to denote "PC" in situations where there is an investment only in Puts and/or Calls. So that includes Puts only, Calls only, and both Puts and Calls. If there is an investment in Stock in any combination, the formula should denote an empty "" value.

So, specifically, the "<3" argument is wrong. I just don't know what to replace it with.

[FDibbins]

Still not sure I understand the full logic here, but consider changing the countif to countifS() which takes more criteria - so you can also test for STOCK?

[okcsteve]

The logic is that I want column J to tell me when the investment is ONLY in the Options (i.e. Put or Call, or Put and Call) and NOT in the Stock.

I will work on COUNTIFS. Thanks for the suggestion.

[JeteMc]

Try pasting the following into cell J5 and copying down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[okcsteve]

JeteMc - Thank you for the response and so sorry for my delayed response. I am out of the country and without access to my computer. I am responding on my phone. I will look at your suggestion upon my return on Thursday when I can get to Excel and then respond back. Thanks again.