CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasifs
Norvin
Norvin

Reputation: 27

Working formula broken after applying Arrayformula

Does anyone know why a working formula can be broken by Arrayformula?
Below is the formula that is working.

=IFS(
AND(LEN(K:K)=0,LEN(O:O)=0,LEN(S:S)=0),"No Grade",
AND(LEN(K:K)>1,LEN(O:O)=0,LEN(S:S)=0),"1 Grade",
AND(LEN(K:K)>1,LEN(O:O)>1,LEN(S:S)=0),"2 Grade",
AND(LEN(K:K)>1,LEN(O:O)>1,LEN(S:S)>1),"3 Grade")

The result after applying Arrayformula below is "No match" and it applys to 1 cell only.

=ArrayFormula(IFS(
AND(LEN(K:K)=0,LEN(O:O)=0,LEN(S:S)=0),"No Grade",
AND(LEN(K:K)>1,LEN(O:O)=0,LEN(S:S)=0),"1 Grade",
AND(LEN(K:K)>1,LEN(O:O)>1,LEN(S:S)=0),"2 Grade",
AND(LEN(K:K)>1,LEN(O:O)>1,LEN(S:S)>1),"3 Grade"))

Upvotes: 1

Views: 42

Answers (1)

player0
player0

Reputation: 1

ARRAYFORMULA does not support AND fx. you need to use 0/1 logic:

=ARRAYFORMULA(IFS(
(LEN(K:K)=0)*(LEN(O:O)=0)*(LEN(S:S)=0), "No Grade",
(LEN(K:K)>1)*(LEN(O:O)=0)*(LEN(S:S)=0), "1 Grade",
(LEN(K:K)>1)*(LEN(O:O)>1)*(LEN(S:S)=0), "2 Grade",
(LEN(K:K)>1)*(LEN(O:O)>1)*(LEN(S:S)>1), "3 Grade"))

0

Upvotes: 1

Related Questions