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>

 

[Sal Paradise]

Like this?

Excel Workbook
	A	B	C
1	Difficulty	Discrimination	Decision
2	0.62	0.92	Keep
3	0.97	0.47	Keep
4	0.87	0.18	Drop
5	0.79	0.61	Keep
6	0.13	0.25	Keep
7	0.09	0.6	Keep
8	0.44	0.55	Keep
9	0.33	0.33	Keep
10	0.79	0.77	Keep
11	0.65	0.32	Keep
Sheet1

 

[Darren Landon]

That didn't work. There is still an issue with the criteria.

For example, a Difficulty with a .528 and a Discimination with a .453 is calculating a "Drop".

 

[Sal Paradise]

From line 8 of my example:

0.44 0.55 Keep

There are 2 criteria:
If difficulty is over .9, keep it.
If discrimination is over .2, keep it.
Otherwise, drop.

 

[kroz]

Maybe it would be easier if he defines the conditions in a cell. This way you can automatically update the cells without changing the formulas:

=IF(OR(A2>D2,B2>E2),"Keep","Drop")

where D2 and E2 are the cells where you insert your conditions

 

[Sal Paradise]

The problem is that his conditions look like they could get more complex. While he writes 5 of them in the OP, there are actually only 2 (any discrimination over .2 passes, as does any difficulty over .9). In the future, if he makes it like those 5, the formula would have to be reworked anyway.

 

[Darren Landon]

Based on that logic you would be correct, but the conditions for line 4 are causing a problem. Any condition below .60 and above .20 is being dropped and it should be a keep.

I also need to add another criteria. In addition to Difficulty and Discrimination, I need to add Reliability, where anything above .10 is a "Keep", otherwise "Drop".

 

[kroz]

This might be what you are looking for

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

And this to consider reliability too:

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

 

[Sal Paradise]

Please look at my post again.

See lines 8 and 9. Both have values under .6 and over .2, and both are saying "Keep"

Please post whatever formula you are working, because if you are having that problem it is not equivalent to my formula as written above.

 

[Jon von der Heyden]

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"}))