New answers tagged google-sheets-arrayformula
0
You are supposed to use a cell reference of the cell you have a value in so instead =15*12 you do =A$1*12 and drag down:
if you don't want to play with dragging you need to use array formula:
=ARRAYFORMULA(IF(ROW(A1:A10), A$1*12, ))
0
=ARRAYFORMULA(TRANSPOSE(QUERY(TEXT(TO_DATE(ROW(
INDIRECT("A"&DATE(YEAR(A1), MONTH(A1), DAY(A1))):
INDIRECT("B"&EOMONTH(A1, 0)))), "ddd"),
"where Col1 = 'Mon' or Col1 = 'Tue' or Col1 = 'Wed'")))
1
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
0
A sneaky way would be to ask the respondent to enter a current time as the first question of your form and the actual submission time will act as the end time. Then you just run a simple ARRAYFORMULA in your spreadsheet to get the duration:
=ARRAYFORMULA(IF(LEN(A20:A), A20:A-B20:B, ))
0
paste bob, china, bing, bong into A1 cell
paste this formula into B1 cell
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(A1; ","))))
select B1:B4 range
press CTRL + C
delete A1 and B1 cells
press CTRL + SHIFT + V where you need it
0
=ARRAYFORMULA(ARRAY_CONSTRAIN(VLOOKUP(QUERY({ROW(B1:B20),
RANDBETWEEN(ROW(B1:B20)^0, 999^99)}, "select Col1 order by Col2"),
{ROW(B1:B20), B1:B20}, 2, 0), 1, 1))
0
If you want to always get a positive value you need to use the absolute value function. In the simplest case you just use =abs(value). But that value can be a number, a cell reference or even a formula.
The absolute value function will always return a positive value. In your case just wrap the =K4-P4 with the ABS() function which would be =ABS(K4-P4)
0
=ARRAYFORMULA(QUERY(IF(QUERY(QUERY(SPLIT(
TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A4&CHAR(9)), COUNTA(A1:A4)), CHAR(9)))&" "&
TRANSPOSE(SPLIT(CONCATENATE(REPT(A1:A4&CHAR(9), COUNTA(A1:A4))), CHAR(9)))," "),
"where Col1 <> Col2", 0), "select Col1", 0)<
QUERY(QUERY(SPLIT(
TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A4&CHAR(9)), COUNTA(A1:A4)...
-1
=ARRAYFORMULA(IF(LEN(K4:K&P4:P), IF(K4:K-P4:P<0, P4:P-K4:K, K4:K-P4:P), ))
0
ARRAYFORMULA does not support GOOGLETRANSLATE. The best you can have is to pre-program the array like this:
={GOOGLETRANSLATE(B20, "en", "de");
GOOGLETRANSLATE(B21, "en", "de");
GOOGLETRANSLATE(B22, "en", "de");
GOOGLETRANSLATE(B23, "en", "de");
GOOGLETRANSLATE(B24, "en", "de")}
Another workaround would be to join stuff:
=ARRAYFORMULA(TRIM(...
0
first, run one IMPORTRRANGE to connect the sheets:
=IMPORTRANGE("ID"; "MY!A2")
then try:
=QUERY({{ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(
QUERY(IMPORTRANGE("ID"; "MY!A2:A"); "select A where A is not null")))))\
QUERY(IMPORTRANGE("ID"; "MY!A2:A"); "select A where A is not null")};
{ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(
QUERY(...
1
=IFERROR(SUM(ARRAYFORMULA(QUERY(
SPLIT(TRIM(TRANSPOSE(SPLIT(REGEXREPLACE(C3, "((.*))", ""), ",")&"x1")), "x"),
"select Col1*Col2*Col3 label Col1*Col2*Col3''", 0))))
0
=ARRAYFORMULA(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(SPLIT(REPT(TRANSPOSE(
QUERY(TRANSPOSE(A1:C2&"♂"),,999^99))&"♀",
COUNTA(Sheet2!A3:A)), "♀")),,999^99)), "♂"))
0
To add columns based on data, use the Spreadsheet Service or the Spreadsheet Advanced Service of Google Apps Script.
You should decide how the code will be called. This could be from the Apps Script editor, from custom Google Sheets UI buttons, menus, dialogs, sidebars or by using triggers.
As an alternative you could use Google Sheets add-on or the ...
1
delete everything in range D:Z
paste to D2 cell:
=UNIQUE(A2:A)
paste to E2 cell:
=ARRAYFORMULA(IF(LEN(D2:D);
MMULT(IFERROR(LEN(G2:Z)/LEN(G2:Z); 0); TRANSPOSE(COLUMN(G2:Z2)^0)); ))
paste to G2 cell:
=TRANSPOSE(UNIQUE(FILTER(B$2:B; A$2:A=D2)))
paste to G3 cell and drag down:
=TRANSPOSE(UNIQUE(FILTER(FILTER(B$2:B; A$2:A=D3);
NOT(COUNTIF(INDIRECT("G2:"&...
0
=QUERY({B2:C}; "select Col1,sum(Col2)
where Col1 !=''
group by Col1
label sum(Col2)''"; 0)
=ARRAYFORMULA({"Month"\ "Total"; {
QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2)
where toDate(Col1) is not null
group by toDate(Col1)
label ...
0
for "sub-total" count you can use:
=ARRAYFORMULA(IF(A2:A="Sub-Total", COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)), ))
0
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(IF(LEN(A1:A),
"♠"&A1:A&"♦"&B1:B&"♦"&C1:C&"♠"&A1:A&"♦"&B1:B&"♦"&D1:D&"♠"&A1:A&"♦"&B1:B&"♦"&E1:E, )
,,999^99), "♠")), "♦")))
0
delete your A column and paste this into A3 cell:
={GOOGLEFINANCE("ASX:EXL", "close", TODAY()-470, TODAY());
QUERY(GOOGLEFINANCE("ASX:EXL", "close", TODAY()-470, TODAY()),
"select Col1 offset "&(COUNTA(
GOOGLEFINANCE("ASX:EXL", "close", TODAY()-470, TODAY()))/2)-1, 0)+IF(WEEKDAY(QUERY(
GOOGLEFINANCE("ASX:EXL", "close", TODAY()-470, TODAY()),"select ...
0
you can construct a virtual table from columns you want:
=IFERROR(VLOOKUP($A2, {'ac6'!$D:$D, 'ac6'!$L:$L;
'ac6'!$F:$F, 'ac6'!$L:$L;
'ac6'!$H:$H, 'ac6'!$L:$L}, 2, 0), 0)
=ARRAYFORMULA(IFERROR(VLOOKUP($A2:$A, {'ac6'!$D:$D, 'ac6'!$L:$L;
'ac6'!$F:$F, 'ac6'!$L:$L;
...
0
not all regular formulas can be converted into array formulas. in this case, you can go only semiautomatic like:
={TRANSPOSE(UNIQUE(TRANSPOSE(A2:D2)));
TRANSPOSE(UNIQUE(TRANSPOSE(A3:D3)));
TRANSPOSE(UNIQUE(TRANSPOSE(A4:D4)));
TRANSPOSE(UNIQUE(TRANSPOSE(A5:D5)));
TRANSPOSE(UNIQUE(TRANSPOSE(A6:D6)))}
1
=ARRAYFORMULA(IF(LEN(A3:A),
MMULT(IFERROR(LEN(B3:E)/LEN(B3:E), 0), TRANSPOSE(COLUMN(B2:E2)^0)), ))
0
=ARRAYFORMULA(IF(LEN(B2:B&E2:E), B2:B+E2:E, ))
0
This is usually dealt with the usage of ID system and VLOOKUP formula where you match data based on unique IDs.
0
When you move formulas around, or the cells they pull values from, Sheets does its best to intelligently preserve or update those links. You said "I'm not deleting rows myself in the second sheet." That's true.
Instead, you're accidentally doing the opposite—creating rows in the first sheet—by having a form attached to it. Your second sheet's third cell ...
0
Today Google announced a built-in feature to remove duplicates.
From Three new features to format and work with data in Sheets
What’s changing
We’re introducing three new features that will help you clean up and
work with your data in Sheets. These features are:
Remove duplicates
Trim whitespace
Compatible spreadsheet shortcuts
...
0
Please try, in C4:
=ArrayFormula(if(len(B4:B),B4:B*(choose(match(B4:B,{0,200,400,900}),0,0.1,0.15,0.2)),""))
0
=ARRAYFORMULA(VLOOKUP(QUERY(SPLIT(A2:A, "_"),
"select max(Col2) where Col2 < 800 label max(Col2)''", 0),
{INDEX(SPLIT(A2:A, "_"), , 2), A2:A}, 2, 0))
0
=ARRAYFORMULA(IF(LEN(B2:B), SUBSTITUTE(COUNTIFS(IF((LEN(E2:E)) * (E2:E>0), 1, ),
IF((LEN(E2:E)) * (E2:E>0), 1, ), ROW(B2:B), "<="&ROW(B2:B)), 0, ), ))
0
paste this into C1 cell:
=ARRAYFORMULA({TO_DATE(ROW(
INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), IFERROR(VLOOKUP(TO_DATE(ROW(
INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), A:B, 2, 0))})
then select columns C:D
press CTRL + C
press DELETE
select A1 cell
press CTRL + SHIFT + V
0
yes it is if the "sample" (A2:B6) is large enough:
=ARRAYFORMULA(IFERROR(VLOOKUP(A7:A, A2:B6, 2, 0)))
0
=ARRAYFORMULA({IF((I6:I<>"") * (J6:J<>"") * (H6:H=K$5), TEXT(J6:J-I6:I, "hh:mm"), ),
IF((I6:I<>"") * (J6:J<>"") * (H6:H=L$5), TEXT(J6:J-I6:I, "hh:mm"), )})
demo spreadsheet
0
You could exploit the powers of SUMPRODUCT, much beyond lookups.
Once you get used to it, it is fantastic.
For your entertainment, I would boast of some examples below...
Try the following formula instead:
=SUMPRODUCT((1-(1-($A2='ac6'!$D:$D))*(1-($A2='ac6'!$F:$F))*(1-($A2='ac6'!$H:$H)))*('ac6'!$L:$L)).
The trick of using (1 - (1 - cond1) * (1 - cond2) * ......
0
=IF(AND(H5<>"", I5<>""), I5-H5, )
=ARRAYFORMULA(IF((H5:H<>"") * (I5:I<>""), TEXT(I5:I-H5:H, "hh:mm"), ))
0
You could do a "fix" with SUBSTITUTE fx:
=ARRAYFORMULA(SUMIFS(B:B, SUBSTITUTE(A:A, "'", ""), SUBSTITUTE(D2, "'", "")))
Or use QUERY and have it all in one go:
=QUERY(A1:B, "select A,sum(B) where A !='' group by A label sum(B)'Qty'", 1)
0
This is usually done in post-processing with simple array formula which derives weekday from dates collected by the form.
={"weekday"; ARRAYFORMULA(IF(LEN(A2:A), WEEKDAY(A2:A, 1), ))}
={"weekday"; ARRAYFORMULA(IF(LEN(A2:A), TEXT(A2:A, "dddd"), ))}
0
=REGEXEXTRACT(A1, "http.*")
______________________________________________________
=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(REGEXEXTRACT(D2:D,
"(\d+.\d+,.*)|(http.*)|\((.*)\)"))),,999^99))))
1
=ARRAYFORMULA(IF(LEN(A2:A), TEXT(B2:B-A2:A, "[h]:mm"), ))
=TEXT(AVERAGE(ARRAYFORMULA(IF(LEN(A2:A), B2:B-A2:A, ))), "[h]:mm:ss")
=ARRAYFORMULA(IF(LEN(A2:A), TEXT((B2:B-A2:A)/COUNTA(A2:A), "[h]:mm:ss"), ))
1
1:
=ARRAYFORMULA(IF(LEN(K2:K&Q2:Q&T2:T&W2:W),
IF(K2:K<>"", K2:K,
IF(Q2:Q<>"", Q2:Q,
IF(T2:T<>"", T2:T,
IF(W2:W<>"", W2:W, W2:W)))), ))
2:
=ARRAYFORMULA(IF(A2:A=A1, C1+1, 1))
=ARRAYFORMULA(IF(A2:A=A1, C1:C+1, 1))
3:
=ARRAYFORMULA(IF(TO_TEXT(C2:C)="2019-January", "NEW", ))
4:
=ARRAYFORMULA(IFERROR(VLOOKUP(E2:E, AA2:AA13, 1,...
1
Static:
=ARRAYFORMULA(SUM(QUERY(IF(ISNUMBER(
INDIRECT("E16:F"&ROW()-1)),
INDIRECT("E16:F"&ROW()-1), ),
"select Col2-Col1")))
Dynamic:
paste in C31 cell and drag to the right:
=ARRAYFORMULA(SUM(QUERY(IF(ISNUMBER(
INDIRECT(ADDRESS(16, COLUMN(), 4)&":"&ADDRESS(ROW()-1, COLUMN()+1, 4))),
INDIRECT(ADDRESS(16, COLUMN(), 4)&":"&...
0
=ARRAYFORMULA(IF(LEN(O4:O),
IF(O4:O>=900, O4:O*0.2,
IF(O4:O>=400, O4:O*0.15,
IF(O4:O>=200, O4:O*0.1,
IF(O4:O<=199, O4:O*0, )))), ))
0
Your formula has unnecessary stuff in it that only prolong the overall load time with every new non-empty row until the limit is reached and you got yourself an (Loading...) error.
Paste this formula into any suitable empty cell on your sheet (let's say X1):
=SHEETNAME(TODAY())
And then use this formula instead of yours:
={"Production"; ARRAYFORMULA(IF(...
0
AFAIK Google Sheets custom functions can't be used inside ARRAYFORMULA to return an array of values, but if it was working for you, it's good to know.
As you are using a time execution expensive formula it's very likely that you are having the "Loading..." error because the your script exceeded the corresponding quota, and that the description of the error ...
0
=TEXT(D2/B2, "0.00%")
which can also be:
=TEXT((B3-C3)/B3, "0.00%")
or in one go:
=ARRAYFORMULA(IFERROR(TEXT((B2:B-C2:C)/B2:B, "0.00%")))
0
=SUM(INDIRECT(ADDRESS(4, MATCH("BB", 3:3, 0), 4)&":"&
SUBSTITUTE(ADDRESS(4, MATCH("BB", 3:3, 0), 4), "4", "")))
first 4 is the start of the range for sum
and the 3:3 is the row of headers
0
={"Data", "Count Amount";
ARRAYFORMULA(QUERY({UNIQUE(A3:A),
IFERROR(VLOOKUP(UNIQUE(A3:A),
QUERY(A:B, "select A,count(B)
where A is not null
and B='A'
group by A
label count(B)''", 0), 2, 0), 0)},
"where Col1 is not null"))}
0
={QUERY(ARRAYFORMULA(REGEXREPLACE(TEXT(SUBSTITUTE(TEXT(UNIQUE(IF(LEN(A2:A),
DATE(20&MID(A2:A, 9, 2), LEFT(A2:A, 2), MID(A2:A, 4, 2)), )),
"mm/dd/yyyy"), "12/30/1899",""), "ddd"), ".$", "")),
"select Col1,count(Col1) where Col1<>'' group by Col1 label count(Col1)''"),
QUERY(QUERY({ARRAYFORMULA(IF(LEN(A2:A),
TEXT(DATE(IF(LEN(A2:A), 20, )&...
0
let's say your form is: https://forms.gle/7TrRqt8V4Uc7FBTa9
and Sheet1 is pre-populated like:
then paste this into C1 cell on Form Responses 1 sheet:
={"Attendance", "Team Colour", "Meal Choice";
ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet1!A:D, {2,3,4}, 0)))}
demo spreadsheet
0
If you really really want to do it without Sheet2 you can do so too. Delete columns M:O and paste in cell M1:
={""\""\""; "Zusammenfassung"\"Zusammenfassung"\"Zusammenfassung"; "Männlich:"\
"Webilich:"\"Sonstige:";COUNTIF(D:D;"männlich")\COUNTIF(D:D;"weiblich")\COUNTIFS(D$2:D;
"<>männlich";D$2:D;"<>weiblich";D$2:D;"<>");""\""\"";"Pulli:"\"Pulli:"\"...
0
You need to crate Sheet2 and move columns M:Q there and then call it back from Sheet2 with ARRAYFORMULA.
Delete everything in column K and paste this in K1 cell:
={"Preis pp.";
ARRAYFORMULA(IF($E2:E = "Ja"; $Q$3; )+
IF($G2:G = "Ja"; $Q$6; )+
IF($I2:I = "Ja"; $Q$9; ))}
Delete everything in columns M, N, O, Q and paste this in ...
Top 50 recent answers are included
