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.
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?
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
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
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:
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.
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:
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.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.