Complicated 3 Part If And Or

[cchap]

Hi there,

I'm working on building a management tool that can determine which group completed an activity from a large data set with many variables. I currently have three if statements, and I'm wondering if there's anyway to combine them as opposed to having three columns with each of the formulas.

=IF(AND(F2="C",G2="M")*OR(J2="UNCF",J2="CONF",J2="ANTC"),"y","n")
- column F is "C"
- column G is "M"
- column J is "UNCF", "CONF", or "ANTC"
then the activity was done by group 1.

=IF(AND(F3="U",G3="M")*OR(J3="PAID",J3="PRPD"),"y","n")
- column F is "U"
- column G is "M"
- column J is "PAID", or "PPRD"
then the activity was done by group 2.

=IF(AND(F4="U",G4="M")*OR(J4="CONF",J4="UNCF"),"y","n")
- column F is "U"
- column G is "M"
- column J is "CONF", or "UNCF"
then the activity was done by group 3.

So essentially what I would like is one formula that determines which group completed the activity. Any help would be much appreciated!!

[TMS]

Try:

Please Login or Register  to view this content.

[Jonmo1]

You can combine them pretty easily..

Take the first formula
=IF(AND(F2="C",G2="M")*OR(J2="UNCF",J2="CONF",J2="ANTC"),"y","n")
Replace the "y" with "Group 1"
Replace the "n" with the 2nd formula (minus the =)

Now repeat for the y and n in that part,
Replace "y" with "Group 2"
Replace "n" With the 3rd formula (minus the =)

Now repeat for the y and n in that part,
Replace "y" with "Group 3"
Leave the final "n" alone.


Hope that helps.

[cchap]

Awesome!! That worked perfectly:

Now that I have that sorted out...
The Group 1, 2, and 3 are going to be replaced with index matches that look up unit times in a table. For Group 1 and 3 there is only one column to look up the unit time from, for Group 2 there's one column with unit times for data lines that have "US" in column P, and another for data lines that have "CA" in column P. This is what I have so far but I'm not sure how to approach the CA/US:

=IF(AND(F2="C",G2="M")*OR(J2="UNCF",J2="CONF",J2="ANTC"),INDEX(AD4:AD52,MATCH(I2,AB4:AB52,0)),IF(AND(F2="U",G2="M")*OR(J2="PAID",J2="PRPD"),"Group 2",IF(AND(F2="U",G2="M")*OR(J2="CONF",J2="UNCF"),INDEX(AJ4:AJ22,MATCH(I2,AI4:AI22,0)),"n")))