39

In Google Sheets, when using ArrayFormula with AND formula, I don't get the results as it should be.

A|B
2|7

In C1 I put formula as: =and(A1>5,B1>6) then I get True. If in D1 I put formula as: =ArrayFormula(and(A1:A>5,B1:B>6)) I get the results as False.

Here are my two questions:

  1. Why is ArrayFormula not repeated for all cells in the column?
  2. Why do I get true without ArrayFormula and False with Arrayformula?
Tim Williams
  • 122,926
  • 8
  • 79
  • 101
Hossein
  • 411
  • 1
  • 4
  • 5
  • 1
    It's been a long time but as far as I remember, AND doesn't work with array formulas in Excel too. A workaround would be to evaluate the multiplication of the conditions. – ayhan Apr 30 '17 at 20:07

1 Answers1

78

AND doesn't work that way with Array formulae because it ANDs the whole array together in the top left cell, regardless of number of dimensions.

I.e. it checks if "">"" which is FALSE, ANDed with anything it will return FALSE for the top left cell, that result is carried down.

You can use multiplication of truth values to create ANDing that works with ARRAYFORMULA like this:

=ArrayFormula((A1:A>1)*(B1:B>6) = 1)

The OR equivalent would obviously be

=ArrayFormula((A1:A>1)+(B1:B>6) > 0)
MegaMatt
  • 21,542
  • 37
  • 97
  • 141
Robin Gertenbach
  • 8,057
  • 2
  • 23
  • 32