Forum Discussion
=LINEST help
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+J24
It 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