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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi,

Try this:


Excel 2010
ABCDE
1bccTP
2bcbTN
3bbbTN
4bbcFN
5cbcTP
6cbbTN
7cccTP
8ccbFP
9cbFP
10cbFP
11bcFN
12bcFN
Sheet2
Cell Formulas
RangeFormula
E1=IF(OR(A1=C1,B1=C1),IF(C1="c","TP","TN"),IF(OR(A1=B1,A1="",B1=""),IF(C1="c","FN","FP"),"No Match"))


E1 formula copied down.
 
Upvote 0
Hi,

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


Excel 2010
ABCDE
1bccTP
2bcbTN
3bbbTN
4bbcFN
5cbcTP
6cbbTN
7cccTP
8ccbFP
9cbFP
10cbFP
11bcFN
12bcFN
13bNo Match
14cNo Match
Sheet1
Cell Formulas
RangeFormula
E1=IF(OR(A1=C1,B1=C1),IF(C1="c","TP","TN"),IF(AND(A1="",B1=""),"No Match",IF(C1="c","FN","FP")))


E1 formula copied down.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.:)
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top