Sunday, September 15, 2013

yogi_Apply Formula Using LINEST Function To A Dynamically Expanding Set Of Entries In Column E

                                          Google Spreadsheet   Post  #1370
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013
user meph2u (http://productforums.google.com/forum/?zx=64ecnzx8oga3#!category-topic/docs/spreadsheets/DTPS5zVkZuE)
I want to change a range using a value in a cell to reflect when rows are added. How can I use a formula to determine the last row in a column where there is data?
Lets say there are rows of data on columns A-E and I have a formula in F1 (=linest(E1:E25)) that requires knowing the last row.   Can I get a formula to put a value in H1 that is the final row in the E column (in this case 25) and then put that into the function in F1?   Note:  linest does not accept (E1:E) as a range.

I searched for an answer and didn't find one.   But if the solution to this is published and you can point me to the thread, that would be great.

Thanks
---
Here is a spreadsheet

https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdHk2Z1V3bGNvNEUwVlJCdnVPa0VoT1E#gid=0


End goal:  When I add data in row 26, I want the formula in F1 to change from line linest(E1:E25) to linest(E1:E26)
------------------------------------------------------------------------------------------------------------------------------------------------