Create variable function arguments in Google Sheets

Question

I know that my question illustrates a fundamental lack of understanding, but I have hacked together a sheet that almost works except...

I am importing data from googlefinance then operating on the data. The problem is that the length of the data is not always the same. So my problem boils down to this:

Assume column A and B contains data e.g. A1-A10 contains date and B1-B10 contains price, cell c1 contains a COUNT of the data in column A so I know how many data points.

=TREND(B1:B10,A1:A10) will result in a trend line if I get all 10 data points and fail if not

How can I modify the TREND function arguments so that "A10" is instead "A(0 + the contents of C1)"?

Answer 1

Use a combination of ADDRESS() and INDIRECT

• Cell C1 = count of the number of rows. This can be used to generate the second half of a range.
  • Let's assume that the value in C1 = 9.

ADDRESS

• address($C$1,1,4,true) = A9
• address($C$1,2,4,true) = B9
• add a prefix of "A1:" and "B1:" to generate the string value of the ranges
• "A1:"&address($C$1,1,4,true) = A1:A9
• "B1:"&address($C$1,2,4,true) = B1:B9

INDIRECT

• INDIRECT returns a cell reference specified by a string.
  • indirect("B1:"&address($C$1,2,4,true)) - returns values in the range B1:B9
  • indirect("A1:"&address($C$1,1,4,true)) - returns values in the range A1:A9

TREND

=TREND(indirect("B1:"&address($C$1,2,4,true)),indirect("A1:"&address($C$1,1,4,true)))