ArrayFormula and "AND" Formula in Google Sheets
Google SheetsArray FormulasGoogle Sheets Problem Overview
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:
- Why is
ArrayFormula
not repeated for all cells in the column? - Why do I get
true
withoutArrayFormula
andFalse
withArrayformula
?
Google Sheets Solutions
Solution 1 - Google Sheets
AND
doesn't work that way with Array formulae because it AND
s the whole array together in the top left cell, regardless of number of dimensions.
I.e. it checks if "">""
which is FALSE
, AND
ed 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 AND
ing 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)
Solution 2 - Google Sheets
Another a little simpler way:
=ARRAYFORMULA(
IF(H3:H = I3:I = J3:J = TRUE; 1; 0)
)
Solution 3 - Google Sheets
AND
doesn't go with an Arrayformula
. However, there is an interesting way to use AND
and OR
operators without really using them.
While using arrayformula
, in order to do an AND
function you must multiply the 2 conditions, for example
=arrayformula(if((condition1)*(condition2), value if true, value if false))
Similarly using OR
functions is identical EXCEPT instead of a "*" Symbol you would ADD the 2 conditions.
=arrayformula(if((condition1)+(condition2), value if true, value if false))