Complex "IF" statement help!!!

[Darren Landon]

Can anyone help with a formula for the following conditions? I can do simple IF formulas, but this one s beyond my experience level.

If the Difficulty and Discrimination conditions are met, a Keep/Drop decision needs to be made.

Thank you very much for any help...


Difficulty Discrimination Decision
<TABLE class=MsoNormalTable style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 512.2pt; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse; mso-border-alt: solid black 1.5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=683 border=1><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1.5pt solid; WIDTH: 32.6pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" width=43>1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 124.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black 1.0pt" width=166>Higher than .90<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 99pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt" width=132>Any Value<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 229.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt" width=306>Keep
</TD><TD style="BORDER-RIGHT: black 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1.5pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 11.8pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" vAlign=top width=16><o> </o>
</TD></TR><TR style="mso-yfti-irow: 1"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1.5pt solid; WIDTH: 32.6pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid black 1.0pt" width=43>2<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 124.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black 1.0pt; mso-border-top-alt: solid black 1.0pt" width=166>Between .60 and .90<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 99pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-top-alt: solid windowtext 1.0pt" width=132>Above .20<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 229.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-top-alt: solid windowtext 1.0pt" width=306>Keep
</TD><TD style="BORDER-RIGHT: black 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 11.8pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" vAlign=top width=16><o> </o>
</TD></TR><TR style="mso-yfti-irow: 2"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1.5pt solid; WIDTH: 32.6pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid black 1.0pt" width=43>3<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 124.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black 1.0pt; mso-border-top-alt: solid black 1.0pt" width=166>Between .60 and .90<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 99pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-top-alt: solid windowtext 1.0pt" width=132>Below .20<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 229.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-top-alt: solid windowtext 1.0pt" width=306>Drop
</TD><TD style="BORDER-RIGHT: black 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 11.8pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" vAlign=top width=16><o> </o>
</TD></TR><TR style="mso-yfti-irow: 3"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1.5pt solid; WIDTH: 32.6pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid black 1.0pt" width=43>4<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 124.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black 1.0pt; mso-border-top-alt: solid black 1.0pt" width=166>Below .60<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 99pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-top-alt: solid windowtext 1.0pt" width=132>Above .20<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 229.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-top-alt: solid windowtext 1.0pt" width=306>Keep
</TD><TD style="BORDER-RIGHT: black 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 11.8pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" vAlign=top width=16><o> </o>
</TD></TR><TR style="mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1.5pt solid; WIDTH: 32.6pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid black 1.0pt" width=43>5<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 124.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black 1.0pt; mso-border-top-alt: solid black 1.0pt" width=166>Below .60<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 99pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-top-alt: solid windowtext 1.0pt" width=132>Below .20<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 229.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-top-alt: solid windowtext 1.0pt" width=306>Drop
</TD><TD style="BORDER-RIGHT: black 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 11.8pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1.5pt solid; BACKGROUND-COLOR: transparent" vAlign=top width=16><o> </o>
</TD></TR></TBODY></TABLE>

 

[kroz]

Maybe there are other conditions that we don't know of .
It looks too easy..

 

[Sal Paradise]

I'm struggling to understand this. What is the relevancy of the difficulty categories?

From what I can gather it is always a drop if the discrimination is below .2 unless difficulty is above .9. Am I right?

If so:

=IF(A2>0.9,"Keep",LOOKUP(B2,{0,"Drop";0.2,"Keep"}))

Also expressable as:
=IF(OR(A2>0.9,B2>0.2),"Keep","Drop")

No? He's telling me my formula is giving the wrong answer though, so I'm rather confused too. It looks like the difficulty has to be over .9 or the discrimination above .2.

 

[Darren Landon]

Thanks Kroz...

=IF(AND(OR(A2>90,AND(A2>60,B2>20),AND(A2<60,B2>20)),C2>0.10),"keep","drop")

This one did the trick.

 

[tusharm]

Well, if this works for you, it works for you but it has nothing to do with your initial post on the subject where none of the numbers were > 1. Now, you have some in the tens and one < 1!

Further, I would never embed my thresholds in a formula because it makes the connection between the application logic and the model much more difficult to comprehend. Instead, put the data in a table such as H2:J6:

Difficulty	Discrimination	
	0	0.2
0	Drop	Keep
0.6	Drop	Keep
0.9	Keep	Keep

Now, if the values of interest are in L2:M2, the formula =INDEX(I4:J6,MATCH(L2,H4:H6,1),MATCH(M2,I3:J3,1)) yields the desired result and the tabular nature of how you described the problem is also retained by the Excel model.

Thanks Kroz...

=IF(AND(OR(A2>90,AND(A2>60,B2>20),AND(A2<60,B2>20)),C2>0.10),"keep","drop")

This one did the trick.

 

[Darren Landon]

I added the third criteria in a later post.