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>
Can anyone help me out to figure solution to this problem
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>
b | c | c | TP | |
b | c | b | TN | |
b | b | b | TN | |
b | b | c | FN | |
c | b | c | TP | |
c | b | b | TN | |
c | c | c | TP | |
c | c | b | FP | |
c | b | FP | ||
c | b | FP | ||
b | c | FN | ||
b | c | FN |
<tbody>
</tbody>
Can anyone help me out to figure solution to this problem