Forum Discussion
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 references and the remaining 3 to be dynamic references. However, Excel only allows to refer to my independant variables as one area (e.g. G3:K22). Ideally, I would like my references to my independant variables to be "G3:I22;$J$3:$K$22" but this will result in Excel to believe that I am typing information for the next argument of the function.
How do I make part of my references to the independant variables locked while the other remain dynamic?
The only other solution I can think of myself is to make the underlying calculations myself but this is quite inconvenient considering the many variables.
I do hope that someone can help. Any input is much appreciated. I have uploaded my sheet in case you find it relevant. It is in column O that that I make my regression.
Thank you in advance.
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())
5 Replies
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?
