New answers tagged google-sheets-arrayformula
0
Make sure to first delete excess rows before using arrayformulas... for example if you have 100 rows of data make sure you don't have more than 200 rows in your speadsheet... (highlight right click and delete excess rows) The following formula will do what you are asking for assuming row 1 is headers.
Paste the following in cell F1
=arrayformula({"Final ...
0
=UNIQUE(
TRANSPOSE(
SPLIT(
CONCATENATE(ARRAYFORMULA(UNIQUE(Week1!B2:B)&CHAR(9)))&
CONCATENATE(ARRAYFORMULA(UNIQUE(Week2!B2:B)&CHAR(9)))&
CONCATENATE(ARRAYFORMULA(UNIQUE(Week3!B2:B)&CHAR(9)))&
CONCATENATE(ARRAYFORMULA(UNIQUE(Week4!B2:B)&CHAR(9))),
CHAR(9))
)
)
Props-Will Cervarich
...
0
You want to build a query where certain criteria are triggered by selection of checkboxes. You have already set up formula to create dynamic lists of the checked criteria.
The data consists of:
the primary table (4 columns x 15 rows)
Criteria 1: Type - 3 x checkboxes, and 1 x dynamic list
Criteria 2: Team - 3 x checkboxes, and 1 x dynamic list
Criteria 3: ...
1
onEdit / onChange triggers aren't triggered by formula result changes, they are only triggered when a user edits the spreadsheet by using the Google Sheets UI.
The solution is instead of monitoring for formula results changes look for the cells being edited by the user whom will make the formula results change.
0
You can wrap your formula with the IFERROR function instead:
=IFERROR(ArrayFormula(JOIN(", ",UNIQUE(FILTER(SOURCERANGE!A1:A99999,SOURCERANGE!N1:N99999=A2)))),"")
You could also try the IFNA function.
1
You need to wrap your sumifs in a sum so like so (due to you using the array trick):
=IFERROR(
SUM(SUMIFs($S$4:$S$5006,
$E$4:$E$5006,
"<"&'$A$1,
$G$4:$G$5006,
{"*C1*","*C2*","*C3*","*C4*","*C5*"},
$B$4:$B$5006,
$A$1,
!$...
1
Start Date Number of Days
3/1/2019 2
Assuming 3/1/2019 is cell A2 use the following formula =arrayformula(sequence(B2+1,1,0)+A2)
and the result will be:
3/1/2019
3/2/2019
3/3/2019
If you want to do weeks a simple modification like this will give you the exact result you asked for:
=arrayformula(sequence(B2+1,1,0,7)+A2)
SEQUENCE
0
Problem solved:
=arrayformula(if(InputData!A:A="","",if(InputData!B:Z="","",InputData!A:A&" -> "&InputData!B:Z)))
1
The formula that would give you the desired results would be:
=ArrayFormula(REGEXREPLACE(ARRAYFORMULA(TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B3:H8),B3:H8&"★",)&REPT(" "&"♣︎",COLUMN(B3:H3)=COLUMN(H3))),"♣︎")))),"([^★]+).+","$1"))
It is a bit convoluted but works fine.
2
EDIT
(following OP's comment)
To exclude empty cells (which are counted as zeros altering the result) we adjust the formula by adding the FILTER function.
=AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(FILTER(A44:H44,A44:H44<>"")),"L",""))*1))
Initial answer
You can use this simple formula:
=AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(A44:H44),"L",""...
1
try this. It's cumbersome but it works
=average(if(isnumber(A1),A1,value(left(A1,len(A1)-1))),if(isnumber(B1),B1,value(left(B1,len(B1)-1))),if(isnumber(C1),C1,value(left(C1,len(C1)-1))),if(isnumber(D1),D1,value(left(D1,len(D1)-1))),if(isnumber(E1),E1,value(left(E1,len(E1)-1))),if(isnumber(F1),F1,value(left(F1,len(F1)-1))),if(isnumber(G1),G1,value(left(G1,len(...
0
Try
=iferror(QUERY(transpose(A2:D2),"Select Col1 where Col1 is not null limit 1",0),"")
2
You can also use this single robust formula in cell B2:
=IFERROR(ArrayFormula(if(LEN(A2:A),TRIM(SPLIT(
REGEXREPLACE(TRANSPOSE(SPLIT(TRANSPOSE(ArrayFormula(join("♣︎", A2:A))),"♣︎")),"(?:.?(@[A-Z][a-z]\b))|(.*)","$1"&" ")," ")
),"")),"")
An even simpler formula can be used if you don't have any empty rows in ...
2
This one is fairly strait forward, please see the code below:
=textjoin(",",true,arrayformula(address(row(B2:D4),column(B2:D4),4)))
You can change the last 4 to a 1 if you want absolute references.
0
You want to display the relevant year that matches a Day/Month value in Column B that is understood to be sorted in descending order.
The format of the Day/Month values are not consistent. Some values are dates while others are strings. This is confirmed by isdate().
There are two formulas:
Cell A1:
=if(row()=1,2019,1)
Cell A2, copy to bottom of page: ...
0
Using split + a filter you can extract the words with the pattern you need : @ + upper case + whatever
=transpose(
filter(
transpose(SPLIT(A2," ")),
REGEXMATCH(transpose(SPLIT(A2," ")),"@[A-Z]")
)
)
Top 50 recent answers are included
