Forum Discussion
Solved
Linre regression references - locked and dynamic cell references
Dear community, For each cell I need to make a linear regression based on the previous 20 observations. I have 5 independent variables and I need 2 of my independent variables to be locked refere...
Than could be
{1,0,0,0,0}*G3:G22+{0,1,0,0,0}*H3:H22+{0,0,1,0,0}*I3:I22+{0,0,0,1,0}*$J$3:$J$22+{0,0,0,0,1}*$K$3:$K$22within
=IFERROR(IF(B3=B23,LINEST(D3:D22,{1,0,0,0,0}*G3:G22+{0,1,0,0,0}*H3:H22+{0,0,1,0,0}*I3:I22+{0,0,0,1,0}*$J$3:$J$22+{0,0,0,0,1}*$K$3:$K$22,TRUE,FALSE),NA()),NA())
Than could be
{1,0,0,0,0}*G3:G22+{0,1,0,0,0}*H3:H22+{0,0,1,0,0}*I3:I22+{0,0,0,1,0}*$J$3:$J$22+{0,0,0,0,1}*$K$3:$K$22within
=IFERROR(IF(B3=B23,LINEST(D3:D22,{1,0,0,0,0}*G3:G22+{0,1,0,0,0}*H3:H22+{0,0,1,0,0}*I3:I22+{0,0,0,1,0}*$J$3:$J$22+{0,0,0,0,1}*$K$3:$K$22,TRUE,FALSE),NA()),NA())
SergeiBaklan thank you for taking time to answer my question.
Interesting solution! That is a very clever approach. However, when testing to see if it returns the same as as when running the regression through data analysis -> regression it does not return the same coefficeints. Do you have any idea as to why that is?
Again, I really appreciate your help.
Perhaps I misunderstood your requirements, but initial function and new one are working on different arrays
Which one shall be used e.g. for the second line with formula?
- My apologies, of course my formula did not return the correct output. I have tested your function and it provides me with the correct output. Thank you very much Sergei Baklan - I really benefitted from this. It is much appreciated!
TobbenDK , you are welcome, glad to help