I use ArrayFormula()
to simplify the way I create my reports.
Instead of having to create a reference in each cell (eg. =C1
,=C2
,=C3
,=C4
in each cell, I just use =arrayformula(C1:C4)
in one single cell. It does exactly same job, but is much simpler and it keeps things more organized, because I just need to look in one cell for possible errors.
It works great when I have to reference a range into another like take the values of C1:C4 into the A1:A4 range. In the A1 cell I would just write =arrayformula(C1:C4)
and it does its magic.
It does get a bit trickier when the ranges are not the same length, but it is feasible nonetheless. For instance, if I want to stack two or more range link C1:C4 on top of B1:B3, on cell A1 I can write =arrayformula({C1:C4;B1:B3})
.
My problem is using arrayFormula()
to copy a repeating pattern. For instance, if I want to copy the content of cell C1 4 times I would use =arrayformula({C1;C1;C1;C1})
.
This would work and would achieve the desired effect. However, I was wondering if there is a better way to do that. Something like =arrayformula({C1}*12)
were this pattern would repeat 12 times. This would also enable me to have a dynamic formula, such as =arrayformula({C1}*count(D:D))
where the pattern would repeat according to some variable.
Do you have any ideia on how to achieve that using only native formula (no javascript)?
For N_rows of value:
=ARRAYFORMULA(IF(SEQUENCE(N_rows),value))
value can be anything here: a string, a number or a formula.
This was suggested here.
Here are some use cases:
N_rows can also be calculated with some other formula: =ARRAYFORMULA(IF(SEQUENCE(COUNTIF(A:A,"Yes"),value))
You can also play with the condition of IF there:
=ARRAYFORMULA(IF(ISODD(SEQUENCE(N_rows)),"odd row","even row"))
or
=ARRAYFORMULA(IF(SEQUENCE(N_rows)<3,"less than 3","greater than or equal 3"))
If you need a two-dimensional output:
=ARRAYFORMULA(IF(SEQUENCE(N_rows,M_columns),value))
You can also play with SEQUENCE parameters start and step.
See SEQUENCE.
For N rows and M columns of string text:
=ARRAYFORMULA("text"&T(SEQUENCE(N_rows, M_columns)))
For N rows and M columns of number 123:
=SEQUENCE(N_rows, M_columns, 123 ,0)