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

​

	0	1	1	1	0	1
Actual	BaseV	Average	CompV	Ease	MPG	Rest
44.1	41.73376	41.35643	35.95	-0.35	36.7	1
23.8	41.16518	40.94199	38.67	0.25	36.2	1
27.4	41.16518	42.50528	34.95	-0.38872	36.2	1
35	40.5966	42.50218	33.44	-0.08	35.7	1
47.8	40.5966	41.69813	33.7	0.6	35.7	0
46.3	40.5966	41.88781	35.71	-0.26	35.7	1
36.9	41.16518	44.80577	37.04	0.6	36.2	0
37.8	41.73376	39.543	37.02222	-0.10181	36.7	4
38.8	41.39261	44.93608	37.1	0.066058	36.4	3
40.8	40.74429	39.32746	37.2	-0.6	37.35	1
49.7	34.57	39.10413	37.55	0.77	30.4	0
33.8	40.05	37.25455	38.48	-0.19	35.4	1
39	43.62	39.58	39.93	-0.17	36.4	1

<colgroup><col width="67" span="7" style="width:50pt"> </colgroup><tbody>
</tbody>

 

[smeador]

I have tried using various Index functions and Vlookups and Match functions but I am afraid I have not yet had success.

 

[shg]

0​

1​

1​

1​

0​

1​

​

​

​

​

​

​

​

​

​

Actual​

BaseV​

Average​

CompV​

Ease​

MPG​

Rest​

-0.25517​

0​

5.261621​

-1.26661​

-0.98279​

0​

125.7181​

I2:O2: {=LINEST(A3:A15, B1:G1*B3:G15)}

44.1​

41.73376​

41.35643​

35.95​

-0.35​

36.7​

1​

23.8​

41.16518​

40.94199​

38.67​

0.25​

36.2​

1​

27.4​

41.16518​

42.50528​

34.95​

-0.38872​

36.2​

1​

35​

40.5966​

42.50218​

33.44​

-0.08​

35.7​

1​

47.8​

40.5966​

41.69813​

33.7​

0.6​

35.7​

0​

46.3​

40.5966​

41.88781​

35.71​

-0.26​

35.7​

1​

36.9​

41.16518​

44.80577​

37.04​

0.6​

36.2​

0​

37.8​

41.73376​

39.543​

37.02222​

-0.10181​

36.7​

4​

38.8​

41.39261​

44.93608​

37.1​

0.066058​

36.4​

3​

40.8​

40.74429​

39.32746​

37.2​

-0.6​

37.35​

1​

49.7​

34.57​

39.10413​

37.55​

0.77​

30.4​

0​

33.8​

40.05​

37.25455​

38.48​

-0.19​

35.4​

1​

39​

43.62​

39.58​

39.93​

-0.17​

36.4​

1​

 

[smeador]

This returns the coefficients, but the "unused" columns are turned into zeroes instead. This ends up throwing off the R Squared Value. Is there anyway to exclude the columns completely rather than turning them into 0's?

 

[yesterdays]

A

B

C

D

E

F

G

H

I

J

K

L

M

1

0​

1​

1​

1​

0​

1​

2

Actual​

BaseV​

Average​

CompV​

Ease​

MPG​

Rest​

-0.25517​

5.261621​

-1.26661​

-0.98279​

125.7181​

3

44.1​

41.73376​

41.35643​

35.95​

-0.35​

36.7​

1​

4

23.8​

41.16518​

40.94199​

38.67​

0.25​

36.2​

1​

5

27.4​

41.16518​

42.50528​

34.95​

-0.38872​

36.2​

1​

6

35​

40.5966​

42.50218​

33.44​

-0.08​

35.7​

1​

7

47.8​

40.5966​

41.69813​

33.7​

0.6​

35.7​

0​

8

46.3​

40.5966​

41.88781​

35.71​

-0.26​

35.7​

1​

9

36.9​

41.16518​

44.80577​

37.04​

0.6​

36.2​

0​

10

37.8​

41.73376​

39.543​

37.02222​

-0.10181​

36.7​

4​

11

38.8​

41.39261​

44.93608​

37.1​

0.066058​

36.4​

3​

12

40.8​

40.74429​

39.32746​

37.2​

-0.6​

37.35​

1​

13

49.7​

34.57​

39.10413​

37.55​

0.77​

30.4​

0​

14

33.8​

40.05​

37.25455​

38.48​

-0.19​

35.4​

1​

15

39​

43.62​

39.58​

39.93​

-0.17​

36.4​

1​

<tbody>
</tbody>

I2: =IFERROR(INDEX(LINEST($A$3:$A$15,$B$1:$G$1*$B$3:$G$15),SMALL(TRANSPOSE(ROW(1:7))*$A$1:$G$1,COUNTIF($A$1:$G$1,0)+COLUMN()-8)),"")
confirm with Ctrl Shift Enter
Drag until return blank

 

[yesterdays]

Ok Ignore above post , try this one :

I2 : =IFERROR(LINEST($A$3:$A$15,INDEX($B$3:$G$15,0,SMALL(TRANSPOSE(ROW(2:7))*$B$1:$G$1,COUNTIF($A$1:$G$1,0)+COLUMN()-8)-1)),"")
Ctrl shift enter
Drag to right until see blank
And shg, I think your formula wrong some where...
the result shout be

-0.298422874

-0.80491687

4.48245178

-1.30686275

<tbody>
</tbody>

Not

-0.25517​

5.261621​

-1.26661​

-0.98279​

125.7181​

<tbody>
</tbody>

 

[smeador]

Running data analysis regression manually on the columns with a 1 above them as detailed in the previous post (copy and pasting the cells to be adjacent) returns the following numbers:

Intercept	Average	CompV	Ease	Rest
125.718072	-0.9827916	-1.2666056	5.261621	-0.25517

<colgroup><col span="3"><col span="2"></colgroup><tbody>
</tbody>

I want an intercept to be calculated normally, and I need the statistics returned so that I can see the R squared value. Yesterdays, I was unable to retrieve the Stats from your formula - even after adding the appropriate "True" values.

 

[yesterdays]

Ok you could use the formula in post #5 of this thread
That should return value as your

I'm not really understand how LINEST work..
to calculate of "Rest" we use =LINEST(A3:A15,G3:G15) right?
Where is "Intercept" coming from ?

 

[smeador]

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.414088
R Square	0.171469
Adjusted R Square	-0.2428
Standard Error	8.431443
Observations	13

<colgroup><col span="2"></colgroup><tbody>
</tbody>

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	125.7181	90.14859	1.394565	0.200653	-82.165	333.6011	-82.165	333.6011
Average	-0.98279	1.267997	-0.77507	0.460589	-3.9068	1.941214	-3.9068	1.941214
CompV	-1.26661	1.466901	-0.86346	0.413023	-4.64929	2.116075	-4.64929	2.116075
Ease	5.261621	6.460044	0.814487	0.438937	-9.63527	20.15851	-9.63527	20.15851
Rest	-0.25517	2.361612	-0.10805	0.916617	-5.70106	5.190715	-5.70106	5.190715

<colgroup><col width="67" span="9" style="width:50pt"> </colgroup><tbody>
</tbody>

Above is the output if the regression is run through data analysis on Excel. "Average", "CompV", "Ease", and "Rest", are all the X Value columns that had a 1 on top of them indicating that they were to be used in the calculation. These are what the numbers should be, and the LINEST() function is designed to output these regression statistics.

The LINEST() function used on the same data set outputs the following:

-0.25517	5.261621	-1.26661	-0.98279	125.7181
2.361612	6.460044	1.466901	1.267997	90.14859
0.171469	8.431443	#N/A	#N/A	#N/A
0.413911	8	#N/A	#N/A	#N/A
117.6985	568.7138	#N/A	#N/A	#N/A

<colgroup><col width="67" span="5" style="width:50pt"> </colgroup><tbody>
</tbody>

The top row of this output array are the coefficients, and the third number in the first column (0.171469) is the R squared value for the regression analysis performed. This is the number I desire because I will change the locations of the 1's and 0's above each column in order to maximize the R squared value (in order to make my regression analysis more accurate). I want to be able to do this without copying and pasting the desired cells or manually calculating scenarios for every possibly combination of 0's and 1's above the columns.

thanks

 

[yesterdays]

I think you just break something in my head....