Forum Discussion
=LINEST help
Have a look at the attached sheet. It's your own with just a few modifications. Above the "Lijnschatting" table, I entered the text for the ranges you call for in the LINEST function. The reference will change based on the value in cell J24, which I interpreted as the number of rows to be included. The LINEST function will call for these ranges with the help of INDIRECT.
See if works for you.
Your method seems to indeed achieve what I was trying. Could you, explain how this works?
I've taken a look at the formulas you added and I can't make much of it, so it would be nice if you could explain it to me. This way I will be able to replicate/change it by myself
In J24 you seem to have the number of rows in your data range for your line estimate. I.e. 5 rows in B8:C13.
In M23 I "calculate" the range for Y:
="C8"&":C"&8+J24It simply creates a text by combining (using the &-sing) "C8", ":C" and 8+5. This becomes "C8:C13", being the range for Y. In N23, I do something similar to create the text that references to X (B8:B13). When you add something in B14 and C14, cell J24 becomes 6 and the ranges are automatically expanded to also include row 14. Thus, C8:C14 and B8:B14.
Within the LINEST function, I use INDIRECT. What this does is, look at cells M23 and N23 respectively to find the references to the ranges for Y and X. So, INDIRECT(M23) is the same as C8:C13. Hope this is clear.
Thank you very much, I think be able to replicate this myself now