3
A1:
=ARRAYFORMULA(JOIN("-",TEXT({"7/22","7/28"}+(COLUMN(A1)-1)*7,"M/DD")))
Drag to right.
Two dates are initialized and a column number*7 is added per each column.
TEXT to format dates " m/dd"
JOIN the 2 dates by -
3
Aggregation functions like AVERAGE, SUM and other similar that get as parameters array of values and just return a single value are not able to return array of values by using ARRAYFORMULA.
One alternative is to use the arithmetic operators (+,-,/,*).
If it makes sense to assume that
all ranges are of the same size
there aren't blank cells between non-...
3
Alternatives:
Use ArrayFormula / MMULT functions
Limitations: Some formulas should be "redesigned in order to be used with
ArrayFormula / MMULT or other similar built-in functions.
Related Q&A:
How to use ARRAYFORMULA and SUM together to get a column with the sum of the preceeding columns?
Make a custom function
Limitations:
Only works when ...
2
In array formulas instead of AND and OR functions use * and + respectively.
Example
AND: Instead of
=ArrayFormula(and(A2:A4,B2:B4))
use
=ArrayFormula(A2:A4*B2:B4)
OR: Instead of
=ArrayFormula(or(A2:A4,B2:B4))
use
=ArrayFormula(A2:A4+B2:B4)
2
I ended up doing this the not-so-smart way: instead of making VLOOKUP happen across multiple columns, I copied all of the English terms multiple times and stacked them vertically in one column, then put the German translations vertically next to them. This wasn't exactly elegant and wouldn't have worked on a much larger data set, but it let me run the ...
2
Try
=ARRAYFORMULA(IFERROR(MATCH(A2:A12&ROW(A2:A12),FILTER(A2:A12&ROW(A2:A12),A2:A12<>""),0)))
2
For a conditional running average, assuming all entries in A2:A are grouped:
=ArrayFormula(IFERROR((SUMIF(ROW(A2:A),"<="&ROW(A2:A),B2:B)-HLOOKUP(0,SUMIF(ROW(A2:A),"<"&ROW(A2:A),B2:B),MATCH(A2:A,A2:A,0),0))/(ROW(A2:A)-MATCH(A2:A,A2:A,0)-ROW(A2)+2)))
Before the update to the newest version of Sheets a number of months ago, it would have ...
2
Digging the web I have found a Google Post that states:
Aggregating functions such as sum() will not produce multi-cell
results in an arrayformula() wrapper. To calculate row-by-row totals
of the numeric values in columns A to C from row 2 down, use simple
addition instead of sum():
=arrayformula( A2:A + B2:B + C2:C )
When there are lots ...
2
=UNIQUE({A:A;B:B;C:C})
=ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({A:A;B:B;C:C}),
"select Col1 where Col1 is not null")))
2
This does seem very identical to an older question/answer.
In fact, so close that you can get the two by just taking my previous solution and breaking it apart.
=ARRAY_CONSTRAIN(
SORT(ArrayFormula({TRANSPOSE(F1:1),ABS(TRANSPOSE(F1:1)-B7)}),2,1),1,1) to find the value.
=MATCH(B8,A1:1,0) to find its column "number" where A = 1
I have entered the formula in ...
2
={UNIQUE(FILTER('Sheet 1'!A1:A, 'Sheet 1'!A1:A<>"")),
ARRAYFORMULA(SPLIT(TRANSPOSE(
QUERY(TRANSPOSE(IF(TRANSPOSE(
QUERY(TRANSPOSE(
QUERY({'Sheet 1'!A1:B}, "select count(Col1)
where Col1 !=''
group by Col1
pivot Col2", 0)),
"select Col2, ...
2
all you need to do is QUERY out empty rows:
=QUERY({IMPORTRANGE("1cLqgopAWG...", "Sheet1!A1:B5");
IMPORTRANGE("1564EXoW-s...", "Sheet1!A1:B5")},
"where Col1 is not null")
1
in this conditional-formatting-scenario is =ArrayFormula() kind of not necessary.
to hide #N/A you can use =IFERROR()
to avoid #N/A you can use =ISNUMBER =ISDATE =ISBLANK =ISTEXT etc.
and this custom formula is what you seek:
=IF( AND(ISNUMBER(J1:J);
NOT(ISBLANK(L1:L))); COUNTIFS(J1:J; ">0";
K1:K; ">"&...
1
with one more column:
cell A1: =TEXT(DATE(2018,7,15)+B1,"m/d")&"-"&TEXT(DATE(2018,7,21)+B1,"m/d")
cell B1: 7
cell B2: =B1+7
and then drag down A1 and B2
https://docs.google.com/spreadsheets/d/
1
A script might be your best option because on each change, any random functions are reevaluated.
Go to Tools > Script editor and paste in the following script.
// Append a random value from an array when a form is submitted.
// The event parameter, e, contains the range data to append to the correct row.
function append(e) {
var ss = SpreadsheetApp....
1
try this:
=QUERY('Interview Planning'!A:G; "select D, A, F, G where G = ''"; 1)
and also select column G end set it as plain text
https://docs.google.com/spreadsheets/d/
1
F1:=ARRAYFORMULA(SUBSTITUTE(B15:B861; B15:B861; LEFT(B15:B861; 3)))
and then: =QUERY(A15:H861; "select F, sum(D) group by F")
1
paste this into F5 (for example):
=MAX(ArrayFormula(IF(C5:C12=A2,
MMULT(N(ROW(C5:C12)>=TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2))
-
HLOOKUP(0,
MMULT(N(ROW(C5:C12)>TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2)),
MATCH(VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:C11),ROW(C5:C12),),1,TRUE),
VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:...
1
Replace the formula by its results by using copy & "paste as values".
Select the area of the fantastic ArrayFormula results.
Click on Edit > Copy
Click on Edit > Paste special > Paste values only
Now you could add edit the any cell in the range without losing the values originally retrieved by the fantastic ArrayFormula.
Related Q&A
Allow data ...
1
=ARRAYFORMULA(IF(LEN(A3:A),
MMULT(IFERROR(LEN(B3:E)/LEN(B3:E), 0), TRANSPOSE(COLUMN(B2:E2)^0)), ))
1
I stumbled onto the answer myself, but attached is a sample data sheet in case this helps anyone else with a similar need.
The trick was taking the ending row reference "350" out from all ranges.
https://docs.google.com/spreadsheets/d/1-dxpI3GpLAxkoMyscPRsCwkaec1CWRuX5-5Ej0DjVTo/edit?usp=sharing
1
EXPLANATION OF BAD FORMULA
=MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0) =MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0)
The extra formula starting with a second = is the same as wrapping the entire formula with AND( , ).
=AND(MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0),MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"...
1
INTRODUCTION
I have solved the issues that are found in UPDATE 1 thru 3 in the original question. However, I feel the formula is a little convoluted and that there should be a much simpler answer.
EXPLANATION
I added both an IF and INDEX function to the formula that solves the previous formula adding to many blank rows as well as non-true blank rows that ...
Only top voted, non community-wiki answers of a minimum length are eligible
