CODEWITHSUNDEEP
excelexcel-formula
Haris
Haris

Reputation: 14053

How to fix multiple IF condition?

I need to generate an output from two column like,

 A   B  C
 1   1  TP
 1  -1  FN
-1   1  FP
-1  -1  TN

I used the equation below in C1. 

=IF(A1=B1,IF(A1=1&B1=1,"TP","TN"),IF(A1=-1&B1=1,"FP","FN"))

and the out put is like,

 A   B  C
 1   1  TN
 1  -1  FN
-1   1  FN
-1  -1  TN

I couldn't figure out what's wrong with the above equation.
Any help will be appreciated.

Upvotes: 3

Views: 106

Answers (2)

SCB
SCB

Reputation: 6149

Instead of 

A1=1&B1=1

Try

AND(A1=1,B1=1)

Though even then, since you've already confirmed that A and B either are or aren't the same, you only need to check one of their values in the secondary IF statements.

=IF(A1=B1,IF(A1=1,"TP","TN"),IF(A1=-1,"FP","FN"))

Upvotes: 5

Jerry
Jerry

Reputation: 71548

The issue is what SCB said, that is to have two 'AND'ed conditions, you need to use the AND() function, because otherwise, you get the & concatenator that is, for example your first formula is turning into:

=IF(1=1,IF(1=1&1=1,"TP","TN"),IF(A1=-1&B1=1,"FP","FN"))

Which becomes...

=IF(TRUE,IF(1="11"=1,"TP","TN"),IF(A1=-1&B1=1,"FP","FN"))

Then...

=IF(TRUE,IF(FALSE=1,"TP","TN"),IF(A1=-1&B1=1,"FP","FN"))

Then...

=IF(TRUE,IF(FALSE,"TP","TN"),IF(A1=-1&B1=1,"FP","FN"))

You should change it to:

=IF(A1=B1,IF(AND(A1=1,B1=1)"TP","TN"),IF(AND(A1=-1,B1=1),"FP","FN"))

Or shorter...

=IF(A1=B1,IF(A1=1,"TP","TN"),IF(A1=-1,"FP","FN"))

Though I think a fun way to do the same thing would be:

=IF(A1=B1,"T","F")&IF(B1=1,"P","N")

Since 'like' always gives T, and if B1 is positive, then it's always "P", then concatenate the two results.

Upvotes: 3

Related Questions