New answers tagged google-sheets-arrayformula
2
You have the basic concept right just piece it together step by step. This does the job:
=REGEXREPLACE(JOIN(" ",ARRAYFORMULA(IFERROR(
REGEXEXTRACT(SPLIT(A1," ",TRUE,TRUE),
"https://example.com/(\S+)"),""))),"\s+"," ")
You split the string at whitespaces, this is safe because URLs don't contain white space so you're definitely not breaking them up. This ...
3
With a regex, you can replace each character with itself + ','.
Then use a regular split function.
=split(REGEXREPLACE(A1,"(.)","$0,"),",",true,true)
2
If I remember my highschool days, your expression reads :
Sum of the n from 1 to 30.
I.E. 1000*1.07^1 + 1000*1.07^2 + 1000*1.07^3 + ...
Here's a way to do it :
=sum(ArrayFormula(1000*power(1.07,(ROW(A1:A30)))))
Result is 101 073.04
0
This can be done without hardcoding the columns in the formula. I am going to show you how to make a formula that can work with any number of columns without any change to the formula.
We are going to have the filter checkboxes in the first row. I filled the whole first row with checkboxes.
Note that in Google Sheets we can refer to the whole first row as ...
1
(In your example spreadsheet you have no formulas)
Most likely your formula calculated results are given in TEXT format, so AVERAGE -which needs numbers to make calculations- can NOT give any results and you end up with #DIV/0!.
A quick and easy fix:
To change text cells to number cells, select your range (C2:C) and choose a number format for it by ...
0
You can use the following two formulas (no extra helper columns needed):
For column B:
Place this formula in cell B1
=QUERY(({A1:A5;"HELLO";A6:A10;"HELLO";A11:A15;"HELLO";A16:A20;"HELLO";A21:A25}),"SELECT *")
An easy way to write/extend the formula is to copy the cluster ";HELLO";A21:A25 and repeat it for as long as you need it.
For column C:
Place this ...
1
Formula
=ArrayFormula(MMULT(TRANSPOSE((ROW(B1:B9)<=TRANSPOSE(ROW(B1:B9)))*ISTEXT(B1:B9)*1),ISTEXT(B1:B9)*1))
What you are looking to do is called "running total".
The above formula use arrays, TRANSPOSE, MMULT (matrix multiplication), ISTEXT and unity multiplication (to parse TRUE as 1 and FALSE as 0) instead creating a COUNTA formula chain.
The ...
0
Try this and expand it :
=COUNTA(INDIRECT("B1:B"&ROW(B1)))
Note :
=ArrayFormula(COUNTA(INDIRECT("B1:B"&ROW(B1:B6))))
is
Arrayformula ( COUNTA ( =INDIRECT(B1:B{1;2;3;4;5;6} ) )
=INDIRECT()
Returns a cell reference specified by a string.
Source
0
Here's my solution in a worksheet without using Arrayformula as much as possible. I haven't found a way to avoid having to drag down column B, but that's the only thing that's not perfect. You can delete cells in column A and all the formulae stay intact, as requested:
The formula for Column B is (copy this cell into B1 and drag down)
=IF(ISERROR(MATCH("...
Top 50 recent answers are included
