# Complex "IF" statement help!!!

#### Darren Landon

##### New Member
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>

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

##### Well-known Member
Like this?
Excel Workbook
ABC
1DifficultyDiscriminationDecision
20.620.92Keep
30.970.47Keep
40.870.18Drop
50.790.61Keep
60.130.25Keep
70.090.6Keep
80.440.55Keep
90.330.33Keep
100.790.77Keep
110.650.32Keep
Sheet1

Last edited:

#### Darren Landon

##### New Member
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".

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### New Member
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

##### Board Regular
This might be what you are looking for
Code:
``=IF(OR(A2>90,AND(A2>60,B2>20),AND(A2<60,B2>20)),"keep","drop")``
And this to consider reliability too:
Code:
``=IF(AND(OR(A2>90,AND(A2>60,B2>20),AND(A2<60,B2>20)),C2>0.10),"keep","drop")``

Last edited:

##### Well-known Member
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

##### MrExcel MVP, Moderator
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"}))

Replies
10
Views
718
Replies
0
Views
533
Replies
3
Views
657
Replies
5
Views
2K
Replies
13
Views
2K

1,191,204
Messages
5,985,255
Members
439,953
Latest member
suchitha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back