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
Google Sheets - auto calculate with ArrayFormula - Google Docs Editors Community
How to do array formula?
Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts
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.
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.
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
Hello all. I am working on my first big dataset with Google Sheets. The sheet holds employee clock in/clock out info for a small school staff and I plan to make a new one for each school year. There will be an estimated 24K rows in the sheet by the end of the school year. I am trying to get ahead and apply the formulas to the entire columns without having to drag it down. I have a few array formulas in the sheet now which work as intended. My question is, should I make every column an array formula, or will this mess something up? When should I NOT use an array, and when should I use an array? Thanks in advance!