What is the cleanest way to write an array formula to facilitate the addition of more rows?
I'm sure this is a debatable topic but I'll throw out a couple suggestions.
First one is you could change you data into a table (select the range then insert>table
) you can then refer to the range as Table1[tax]
or whatever name you give it.
The other solution would be to use dynamic named ranges. If you use to many of these it could also slow down your sheet though.
You could define your named range as something like
mytable
=OFFSET($A$1,0,0,COUNT($A:$A),3)
which gives a table with as many rows as column A contains cells and 3 columns wide). For other ways to define this see Ozgrid
Then you can reference the second column using =INDEX(mytable,0,2)
. By using a single named range for the entire table it reduces the number of dynamic names it has to evaluate and should be quicker.
Overall the first method is probably the cleanest way to go, the excel tables will extend as rows are added.