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)
ArrayFormula and "AND" Formula in Google Sheets - Stack Overflow
Can somebody explain array formulas for me in a simple way?
What's the difference between ARRAYFORMULA and just dragging down using relative location?
arrays - How to use an ARRAYFORMULA in Google Sheets that references cells in the same column - Stack Overflow
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)
=ARRAYFORMULA(IF(ISNUMBER(G3:G),IF(AND( G3:G>=7.5,G3:G<=8),"Full Day",IF(AND(G3:G>8,G3:G<24) ,"Full Day+",IF(AND(G3<7.5,G3>=4),"Half Day",IF(G3<4,"Short Leave",)))),))
Since you use the ARRAYFORMULA function you should use * instead of the AND function.
=ARRAYFORMULA(IF(ISNUMBER(A3:A),IF((A3:A>=7.5)*(A3:A<=8),"Full Day",IF((A3:A>8)*(A3:A<24) ,"Full Day+",IF((A3<7.5)*(A3>=4),"Half Day",IF(A3<4,"Short Leave",)))),))
When using the ARRAYFORMULA function you should use * instead of the AND function and + instead of the OR function.
Explanation
I don't recall an official site about this right now. In any case.
Point 1
Within an arrayformula, AND gives a single value. Not an array of TRUE/FALSE.
Point 2
You must also remember that in "math language", TRUE=1 and FALSE=0
Meaning
+----------+--------+
| Formula | Result |
+----------+--------+
| =TRUE+2 | 3 |
| =FALSE+2 | 2 |
+----------+--------+
As you can see one can interchange between boolean TRUE/FALSE and 1/0 numbers.
Point 3
About the AND function
The AND function returns true if all the provided arguments are logically true and false if any of the provided arguments are logically false.
Putting it all together
In an arrayformula, instead of using AND/OR when making comparisons, we take advantage of the above information.
So, the "multiplication" (A3:A>=7.5)*(A3:A<=8) will return 1 (meaning TRUE) only if both sides return TRUE. All other conditions return 0 (meaning FALSE).
This is the exact behaviour of the AND function and does work in an ARRAYFORMULA.
About the OR function
The
ORfunction returns true if any of the provided arguments are logically true and false if all of the provided arguments are logically false.
The same logic is applied within the arrayformula, when using + ("addition") instead of the OR function.
No matter how many times I try to read up on it, I can't seem to grasp it.
How are array formulas useful? When do you know you have to use array formulas or when it is smart to use array formulas?
For example, what's the difference between inputting SUM(A1, B1) into C1 and then dragging it down from C1 to C5 versus inputting ARRAYFORMULA(SUM(A1:A5, B1:B5)) into C1?
I tried using ARRAYFORMULA using whole columns (e.g. A:A, B:B, etc.) but if it's a formula where I need a result output to each row
you can always freeze it like:
=INDIRECT("A:A")
this way you can add rows anywhere you want (if you of course not add new row above the row that holds the formula - that would be troublesome to fit in A:A into A2:A)
that the INDEX function just does not work with ARRAYFORMULA at all
INDEX is already ARRAYFORMULA type of formula. the analogy being here as: you need a car to get from A to B where INDEX is a blue car with 3 doors and ARRAYFORMULA is a red car with 5 doors - it doesn't matter what color you have, you just need a car
= IF (G2 <> G1, 0, B1 + 1)
while this is direct logic there are several ways how to achieve the same thing. proper usage would require not to use such formulas as ARRAYFORMULA in column G or B to avoid circular dependency errors. for a simple resetting count up try:
=ARRAYFORMULA(COUNTIFS(B1:B7, B1:B7,
SEQUENCE(ROWS(B1:B7)),"<="&SEQUENCE(ROWS(B1:B7))))
feel free to change B1:B7 to open range or frozen range...
update:
=INDEX(IF(B2:B="";; COUNTIFS(B2:B; B2:B;
SEQUENCE(ROWS(B2:B)); "<="&SEQUENCE(ROWS(B2:B)))-1))
You should use the
INDEX
function with
MATCH
instead of
INDIRECT
in your
ARRAYFORMULA
For example, if your data is in the range
A1:A11
, and you want to retrieve the value at row 5 through
INDIRECT
, you would use:
=INDIRECT("A" & 5)
If you want to retrieve the value at row 5 through
INDEX
, you would use:
=INDEX(A:A, 5)
Now, if you want to use the
INDEX
function with
ARRAYFORMULA
, you will need to use the
MATCH
function to find the row number of the value you want, since
INDEX
only takes a row number as its second argument.
For example, if you want to retrieve the value at row 5 through
ARRAYFORMULA
and
INDEX/MATCH
, you would use:
=ARRAYFORMULA(INDEX(A:A, MATCH(5, ROW(A:A))))
This formula will return the value in
A5
Note: In your example, you're trying to match
$G2
with
$G1:$G1
. If you want to match
$G2
with
$G1:$G3
, you would use:
=ARRAYFORMULA(INDEX(A:A, MATCH($G2, $G1:$G3)))
This formula will return the value from
A1
,
A2
, or
A3
depending on the value of
$G2
I have a sheet listing names with categories. Every time I start a new category, I need the numbering to start from one. I have a code that works well, but I have to copy paste the code every time I add a new row. I'm quite new to google sheets and just found out about ARRAYFORMULA.
This is my current formula:
=IF(B35 = B34, C34+1, "")
-
Column B has the category names (they are always in order...like same category goes in adjacent rows)
-
Column C has the numbering.
-
The formula pasted here is for cell C35.
I already tried with this, but it gives an error.
=ArrayFormula(IF(B35:B = B34:B, C34:C+1, ""))
How do I convert this into ARRAYFORMULA?
Any help would be appreciated.