Linre regression references - locked and dynamic cell references

[TobbenDK]

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.

[#2 · Accepted solution]

@TobbenDK 

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$22

within

=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())

[#3]

@Sergei Baklan  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.

[#4]

@TobbenDK 

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?

[#5]

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!

[#6]

@TobbenDK , you are welcome, glad to help