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)"?

1 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($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 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



  • Really appreciate the clarity of your solution - thank you Mar 26, 2022 at 11:35
  • Glad it works for you. You might consider “accepting” the answer.
    – Tedinoz
    Mar 26, 2022 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.