LINEST on specific non-adjacent columns

[smeador]

Hey guys,

So I am running a daily fantasy regression analysis and I need some help. I have found that certain criteria is better suited for predicting some player's performances than others. So I want to run a solver that will pick which criteria to use to maximize the R squared value.

I listed an example of what it might look like below. I will run binary cells along to top of each column, and I wish to include the columns that come up with a 1 on the top and ignore the columns that come up with a 0 on top. There will be parameters in place to avoid unreal R squared values, but that is not my current concern.

I cannot think of a formula, or a method to use LINEST with the left most column (Actual) as the Y variables and then use each column with a 1 over the top of it as the X variables. Again, columns that show up with a zero above them should not be used in the LINEST.

Thanks a lot,
Shawn

​

[TABLE="width: 469"]
<colgroup><col width="67" span="7" style="width:50pt"> </colgroup><tbody>[TR]
[TD="width: 67"][/TD]
[TD="width: 67, align: right"]0[/TD]
[TD="width: 67, align: right"]1[/TD]
[TD="width: 67, align: right"]1[/TD]
[TD="width: 67, align: right"]1[/TD]
[TD="width: 67, align: right"]0[/TD]
[TD="width: 67, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]Actual[/TD]
[TD="class: xl66"]BaseV[/TD]
[TD="class: xl66"]Average[/TD]
[TD="class: xl66"]CompV[/TD]
[TD="class: xl66"]Ease[/TD]
[TD="class: xl66"]MPG[/TD]
[TD="class: xl66"]Rest[/TD]
[/TR]
[TR]
[TD="align: right"]44.1[/TD]
[TD="align: right"]41.73376[/TD]
[TD="align: right"]41.35643[/TD]
[TD="align: right"]35.95[/TD]
[TD="align: right"]-0.35[/TD]
[TD="align: right"]36.7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]23.8[/TD]
[TD="align: right"]41.16518[/TD]
[TD="align: right"]40.94199[/TD]
[TD="align: right"]38.67[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]36.2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]27.4[/TD]
[TD="align: right"]41.16518[/TD]
[TD="align: right"]42.50528[/TD]
[TD="align: right"]34.95[/TD]
[TD="align: right"]-0.38872[/TD]
[TD="align: right"]36.2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]40.5966[/TD]
[TD="align: right"]42.50218[/TD]
[TD="align: right"]33.44[/TD]
[TD="align: right"]-0.08[/TD]
[TD="align: right"]35.7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]47.8[/TD]
[TD="align: right"]40.5966[/TD]
[TD="align: right"]41.69813[/TD]
[TD="align: right"]33.7[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]35.7[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]46.3[/TD]
[TD="align: right"]40.5966[/TD]
[TD="align: right"]41.88781[/TD]
[TD="align: right"]35.71[/TD]
[TD="align: right"]-0.26[/TD]
[TD="align: right"]35.7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]36.9[/TD]
[TD="align: right"]41.16518[/TD]
[TD="align: right"]44.80577[/TD]
[TD="align: right"]37.04[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]36.2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]37.8[/TD]
[TD="align: right"]41.73376[/TD]
[TD="align: right"]39.543[/TD]
[TD="align: right"]37.02222[/TD]
[TD="align: right"]-0.10181[/TD]
[TD="align: right"]36.7[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]38.8[/TD]
[TD="align: right"]41.39261[/TD]
[TD="align: right"]44.93608[/TD]
[TD="align: right"]37.1[/TD]
[TD="align: right"]0.066058[/TD]
[TD="align: right"]36.4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]40.8[/TD]
[TD="align: right"]40.74429[/TD]
[TD="align: right"]39.32746[/TD]
[TD="align: right"]37.2[/TD]
[TD="align: right"]-0.6[/TD]
[TD="align: right"]37.35[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]49.7[/TD]
[TD="align: right"]34.57[/TD]
[TD="align: right"]39.10413[/TD]
[TD="align: right"]37.55[/TD]
[TD="align: right"]0.77[/TD]
[TD="align: right"]30.4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]33.8[/TD]
[TD="align: right"]40.05[/TD]
[TD="align: right"]37.25455[/TD]
[TD="align: right"]38.48[/TD]
[TD="align: right"]-0.19[/TD]
[TD="align: right"]35.4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD="align: right"]43.62[/TD]
[TD="align: right"]39.58[/TD]
[TD="align: right"]39.93[/TD]
[TD="align: right"]-0.17[/TD]
[TD="align: right"]36.4[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[smeador]

If there would be any other way to approach this I'd also appreciate that.