CODEWITHSUNDEEP
arraysgoogle-sheets-formulaarray-formulas
Captain_D
Captain_D

Reputation: 63

Nested if with array formula Googlesheet

I am trying to use nested if with Array formula but don't understand what is wrong.

According to my experience it should work.

Here is my formula:

=ARRAYFORMULA(IF(AND(G2:G="D",I2:I="Disagree")=true,"FP",IF(AND(G2:G="A",I2:I="Agree")=true,"TN",IF(AND(G2:G="D",I2:I="Agree")=true,"TP",IF(AND(G2:G="A",I2:I="Disagree")=true,"FN","")))))

What I am receiving is blank output.

Upvotes: 0

Views: 42

Answers (2)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(
 IF((G2:G="D")*(I2:I="Disagree")=true, "FP",
 IF((G2:G="A")*(I2:I="Agree")=true,    "TN",
 IF((G2:G="D")*(I2:I="Agree")=true,    "TP",
 IF((G2:G="A")*(I2:I="Disagree")=true, "FN", )))))

Upvotes: 1

idfurw
idfurw

Reputation: 5852

Nested if (to be accurate, AND and OR) is not supported by ARRAYFORMULA, you have to break it down to multiple levels of IF

CHANGE FROM: =ARRAYFORMULA(IF(AND(G2:G="D",I2:I="Disagree"), ...

TO: =ARRAYFORMULA(IF(G2:G="D",IF(I2:I="Disagree", ...

Upvotes: 1

Related Questions