Nesting with IF, AND and OR functions

pnvn95

New Member
Joined
Feb 2, 2016
Messages
10
I am working on a project and I have two columns containing say "b" and "c". There are some conditions and the result of the condition is captured in column C. The problem is as shown below. If any column (A or B) from row one matches the same letter in column C (in this case "c"), then the result is "True Positive (TP). Similarly, for row two, if column A or B in row 2 matches with column C (in this case "b"), then the result should show True Negative (TN). Similarly, for row three, if any of the first two rows matches with column C (here "b"), then it is called True Negative (TN). Using IF, OR and AND statement, I could get this working until I got stumped when there is no data in column A or B (blank) along with column C having opposite letter from one of the columns (A or B) that is filled out. For example, in row 9, if column A is blank and column B is "c" and Column C is "b". The correct answer should be False Positive (FP). For the given formula, it gives me "No Match". Here is how my formulae looks (Couple of them)



Formula 1: =IF(OR(A1=C1,B1=C1),IF(C1="c","TP","TN"),IF(A1=B1,IF(A1="b","FN","FP"),"No Match"))

Formula 2: =IF(OR(AND(A1=C1,A1="b"),AND(B1=C1,B1="b")),"TN",IF(OR(AND(A1=C1,A1="c"),AND(B1=C1,B1="c")),"TP",IF(AND(A1=B1,A1="b",C1="c"),"FN",IF(AND(A1=B1,A1="c",C1="b"),"FP","No Match"))))




<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
bccTP
bcbTN
bbbTN
bbcFN
cbcTP
cbbTN
cccTP
ccbFP
cbFP
cbFP
bcFN
bcFN

<tbody>
</tbody>


Can anyone help me out to figure solution to this problem
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Try this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">b</td><td style=";">c</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">TP</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">b</td><td style=";">c</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">TN</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">b</td><td style=";">b</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">TN</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">b</td><td style=";">b</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">FN</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">c</td><td style=";">b</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">TP</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">c</td><td style=";">b</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">TN</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">c</td><td style=";">c</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">TP</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">c</td><td style=";">c</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">FP</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">c</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">FP</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">FP</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">b</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">FN</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">FN</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">A1=C1,B1=C1</font>),IF(<font color="Red">C1="c","TP","TN"</font>),IF(<font color="Red">OR(<font color="Green">A1=B1,A1="",B1=""</font>),IF(<font color="Green">C1="c","FN","FP"</font>),"No Match"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

E1 formula copied down.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Ignore above post, use this version instead, it'll account for both Column A & B having BLANKS, to result in "No Match":

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">b</td><td style=";">c</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">TP</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">b</td><td style=";">c</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">TN</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">b</td><td style=";">b</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">TN</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">b</td><td style=";">b</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">FN</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">c</td><td style=";">b</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">TP</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">c</td><td style=";">b</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">TN</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">c</td><td style=";">c</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">TP</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">c</td><td style=";">c</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">FP</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">c</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">FP</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">FP</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">b</td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">FN</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">FN</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">No Match</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c</td><td style="text-align: right;;"></td><td style=";">No Match</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">A1=C1,B1=C1</font>),IF(<font color="Red">C1="c","TP","TN"</font>),IF(<font color="Red">AND(<font color="Green">A1="",B1=""</font>),"No Match",IF(<font color="Green">C1="c","FN","FP"</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

E1 formula copied down.
 

pnvn95

New Member
Joined
Feb 2, 2016
Messages
10
Thanks a lot Jtakw for your quick analysis and identifying a solution to my problem. Yes, it works!!! I am thrilled with the result. This forum is indeed a life saver for me.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Thanks a lot Jtakw for your quick analysis and identifying a solution to my problem. Yes, it works!!! I am thrilled with the result. This forum is indeed a life saver for me.
You're welcome, welcome to the forum,

Just to be sure, if either A or B is Blank, but the other is a match to C, if C is c, results in TP, if C is b, results in TN. Is that your desired outcome?
 

pnvn95

New Member
Joined
Feb 2, 2016
Messages
10
Hi jtakw:

That's correct. With either A or B being blank and one of them matches with C (if it is "c" then it is TP and if it is "b", TN). Yes, you provided me with the solution that is accurate and very useful.
 

pnvn95

New Member
Joined
Feb 2, 2016
Messages
10
Hi mfexcel:

Thanks for the link. Yes, I shall try using it by looking at the logic and shall adopt it as the challenges presents. Thanks for sharing this link.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,036
Messages
5,466,150
Members
406,470
Latest member
Darshan Shah

This Week's Hot Topics

Top