Google Spreadsheet ArrayFormula doesn't work

1
votes

the following formula is totally working as a normal formula. As soon as I try to do it as a ArrayFormula all cells stay empty.

=ARRAYFORMULA(IF(AND($L2:$L="yes",ISNUMBER($O2:$O)),WORKDAY.INTL(IF(ISNUMBER($Z2:$Z),$Z2:$Z,IF(ISNUMBER($Y2:$Y),$Y2:$Y,"")),($O2:$O*(IF($A2:$A<>"",IF($P2:$P="day",1,IF($P2:$P="week",5,IF($P2:$P="month",20,0))),"")))),IF(AND($P2:$P<>"",ISNUMBER($O2:$O)),IF(ISNUMBER($Z2:$Z),$Z2:$Z,IF(ISNUMBER($Y2:$Y),$Y2:$Y,""))+$O2:$O,"")))

What am I doing wrong? Thank you.

1
google-sheetsarray-formulas

1 Answers

1
votes

try:

=ARRAYFORMULA(IF( --($L2:$L="yes") * --(ISNUMBER($O2:$O), if true, if false))

and logic doesn't work in ArrayFormula

  • instead of AND use *
  • instead of OR use +
  • -- to convert true to 1 and false to 0