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))
arrays - How to use an ARRAYFORMULA in Google Sheets that references cells in the same column - Stack Overflow
Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts
ArrayFormula and "AND" Formula in Google Sheets - Stack Overflow
How to do array formula?
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$G2
with
G3=ARRAYFORMULA(INDEX(A:A, MATCH(
This formula will return the value from
G1:$G3)))A1
,
A2
, or
A3
depending on the value of
$G2
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 basically got handed a Sheet from the previous person in my role and I immediately noticed that every cell in this table has a formula tied to it, and for the most part it is the same formula but diff cell reference. What I'm thinking is to just replace the formulas with an ARRAYFORMULA at the top of the column but realistically does this actually lighten the load times of the Sheet? I'm having trouble finding documentation on how a Google Sheet actually performs calculations. The goal right now is to lighten up the Sheet so that we can reduce load times, since the Sheet currently is jampacked with tons of IMPORTRANGES and each cell references another cell, which references another cell, and so on.
In Row3 please try:
=ArrayFormula(vlookup(A3:A;KEYS!A$1:B$5;2;0))
What ARRAYFORMULA + FILTER cannot do, BYROW (or BYCOL) + LAMBDA can!
https://support.google.com/docs/answer/12570930
For your Sheet:
=BYROW(A3:A; LAMBDA(row; IFNA(VLOOKUP(row; KEYS!A2:B; 2; FALSE))))
Sample of which: https://docs.google.com/spreadsheets/d/1Bt6AqxfQ9fspvCk4sQsU-gOHbpB7_-MFm-bR1jGF08U/edit#gid=1589326430
Hi all. I am sure this has been asked a hundred times over, but I just can't seem to work it out.
I have two sets of numerical choice questions in a google form. That then populates a sheet. I then would like to add a formula to auto populate the totals of both sets of questions into two totals. Now setting the formula's individually in each row doesn't work because once the form adds new results it wipes the formulas, so I have seen that there is a way to add this via "Array formula" in the header column. However, my cludgy attempt to do this doesn't seem to work. Where am I going wrong?
I have attached screenshots of the spreadsheet with the two columns highlighted so you can see the formula I have created so far.
Thanks so much for reading and helping. Apologies again for the undoubtedly repeated question.
Screenshot 1
Screenshot 2