Arrayformula
=arrayformula(xxxx)
=arrayformula( if (A:A="", "", {do something}) )
Vertical, Horizontal and Matrix
Limitations
=arrayformula( if (A:A="", "", {do something}) )
Vertical, Horizontal and Matrix
- Arrayformula can be used to fill vertical columns. The most common use case everyone should become familiar with quickly.
- ArrayFormulas can be used to fill rows, but cognitively for the designer, they are harder to create
- ArrayFormulas can be used to fill both columns and rows, and that is way powerful, but again quite confusing to design (see Calendar on Vacation Rental manager for example)
Limitations
- Many formulas can be used inside of arrayformula, including arrayformula, but here are some limitations
- You can NOT use:
- query, filter, index, concatenate
- AND or OR
- Workarounds:
- You can use vlookup (instead of index) and you can nest vlookup/hlookup to mimic index
- You can use the concept of truthy/falsy and addition and multiplication for AND and OR
- Running totals - Running totals are problematic as they create circular reference error. The work arounds to this are very creative using row and column number referencing
Multidimensional