Menu
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:
ArrayFormula not repeated for all cells in the column?true without ArrayFormula and False with Arrayformula?
294
There are three basic logical operators: the NOT, the OR, and the AND operator. Google Sheets implement them as NOT(), OR(), and AND() functions, respectively. The NOT() function functions like the NOT logical operator: it flips the value from TRUE to FALSE or from FALSE to TRUE.
IF is a Google Sheets function that acts based on a given condition. You provide a boolean and tell what to do based on whether it's TRUE or FALSE. You can combine IF with other logical functions – AND, OR – to create nested formulas and go over multiple sets of criteria.
You can't use the AND and OR functions in array formulas directly because those functions return a single result, either TRUE or FALSE, and array functions require arrays of results. You can work around the problem by using the logic shown in the previous formula.
In Google Sheets, you can combine VLOOKUP with other functions to do some neat things. For example, combining VLOOKUP with ARRAYFORMULA can be used to lookup multiple pieces of data at once, which will save you steps so you don't have to enter multiple formulas.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
