LINEST/REGRESSION skipping blanks

[smeador]

Hey guys,

This is my first post on the forum and I'm hoping to get some of the same help others have received. I am running a spreadsheet on excel for a daily fantasy basketball algorithm and so far I have succeeded in developing efficient macros to grab particular player data from a number of sites each day. I am not a 'master' of excel, but I have taken a college course and consider myself skilled.

So I am trying to run a multiple regression analysis on the data in an effort to forecast the player's actual fantasy score for the particular day. However, as you can see in the table I have posted below there are blanks in various categories on any given day. Some sites don't post projections for marginal players, and certain calculated statistics are irretrievable for some players on any given day for various reasons. Also, some of the projections/data were not made available to the public on the websites for the first few games due to the lack of predictability in the early season.

I have tried to take bits and pieces from a number of existing forums here but I just cannot seem to get any LINEST function to skip blanks effectively. I have tried using the SLOPE and INTERCEPT functions but it comes back with an error since the KNOWN X's have several columns and the KNOWN Y's has only one column. And yes I have array entered them.

What I need:
A formula that will give me AT LEAST the coefficients of each of the predictor variables and the intercept while skipping blanks. I do not want an entire row to be skipped due to a blank somewhere in the row. If this can be done using simply using the slope and intercept functions I would be thrilled, but I need a specific formula rather than just suggestions (sorry, learning most of this on the fly).

so the format I want: Actual=intercept + m1*AvgFP + m2*BaseV + ..... etc

KNOWN Y's (Actual): A1:A16
KNOWN X's (all other columns): B1:L16

Thanks so much in advance!


[TABLE="width: 804"]
<tbody>[TR]
[TD="width: 67"]Actual[/TD]
[TD="width: 67"]AvgFP[/TD]
[TD="width: 67"]BaseV[/TD]
[TD="width: 67"]BaskMonster[/TD]
[TD="width: 67"]CompV[/TD]
[TD="width: 67"]DvP_ Diff[/TD]
[TD="width: 67"]Ease[/TD]
[TD="width: 67"]Experts[/TD]
[TD="width: 67"]MPG[/TD]
[TD="width: 67"]Rest[/TD]
[TD="width: 67"]RotoGrinders[/TD]
[TD="width: 67"]Wtd_FP[/TD]
[/TR]
[TR]
[TD="align: right"]51.5[/TD]
[TD][/TD]
[TD="align: right"]42.54122[/TD]
[TD="align: right"]41.41687[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0.15[/TD]
[TD][/TD]
[TD="align: right"]36.8[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]56[/TD]
[TD][/TD]
[TD="align: right"]41.84761[/TD]
[TD="align: right"]46.97006[/TD]
[TD="align: right"]51.5[/TD]
[TD][/TD]
[TD="align: right"]0.63[/TD]
[TD][/TD]
[TD="align: right"]36.2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD="align: right"]42.54122[/TD]
[TD="align: right"]44.84416[/TD]
[TD="align: right"]53.75[/TD]
[TD][/TD]
[TD="align: right"]0.32[/TD]
[TD][/TD]
[TD="align: right"]36.8[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42.7[/TD]
[TD][/TD]
[TD="align: right"]41.38521[/TD]
[TD="align: right"]37.32283[/TD]
[TD="align: right"]48.17[/TD]
[TD][/TD]
[TD="align: right"]-0.87[/TD]
[TD][/TD]
[TD="align: right"]35.8[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]56.5[/TD]
[TD][/TD]
[TD="align: right"]41.38521[/TD]
[TD="align: right"]39.2972[/TD]
[TD="align: right"]46.8[/TD]
[TD][/TD]
[TD="align: right"]-0.17[/TD]
[TD][/TD]
[TD="align: right"]35.8[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]45.4[/TD]
[TD][/TD]
[TD="align: right"]42.54122[/TD]
[TD="align: right"]39.6679[/TD]
[TD="align: right"]48.74[/TD]
[TD][/TD]
[TD="align: right"]-0.24[/TD]
[TD][/TD]
[TD="align: right"]36.8[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27.7[/TD]
[TD][/TD]
[TD="align: right"]41.38521[/TD]
[TD="align: right"]42.24223[/TD]
[TD="align: right"]48.18[/TD]
[TD][/TD]
[TD="align: right"]-0.03[/TD]
[TD][/TD]
[TD="align: right"]35.8[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25.1[/TD]
[TD][/TD]
[TD="align: right"]41.15401[/TD]
[TD="align: right"]42.20842[/TD]
[TD="align: right"]45.54[/TD]
[TD][/TD]
[TD="align: right"]-0.3[/TD]
[TD][/TD]
[TD="align: right"]35.6[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]39.5[/TD]
[TD][/TD]
[TD="align: right"]41.15401[/TD]
[TD="align: right"]44.82758[/TD]
[TD="align: right"]42.9875[/TD]
[TD][/TD]
[TD="align: right"]0.625816[/TD]
[TD][/TD]
[TD="align: right"]35.6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]40.94[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]56.3[/TD]
[TD][/TD]
[TD="align: right"]37.68597[/TD]
[TD="align: right"]40.28643[/TD]
[TD="align: right"]42.6[/TD]
[TD][/TD]
[TD="align: right"]0.628854[/TD]
[TD][/TD]
[TD="align: right"]32.6[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD="align: right"]39.53559[/TD]
[TD="align: right"]40.72609[/TD]
[TD="align: right"]43.97[/TD]
[TD][/TD]
[TD="align: right"]-0.03913[/TD]
[TD][/TD]
[TD="align: right"]34.2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]44.51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]32.2[/TD]
[TD="align: right"]37.22357[/TD]
[TD="align: right"]36.61097[/TD]
[TD="align: right"]42.60909[/TD]
[TD="align: right"]0.058422[/TD]
[TD][/TD]
[TD="align: right"]32.2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]41.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]63.7[/TD]
[TD="align: right"]41.6[/TD]
[TD="align: right"]39.54[/TD]
[TD="align: right"]33.98[/TD]
[TD="align: right"]41.74[/TD]
[TD="align: right"]-0.0173[/TD]
[TD="align: right"]-0.61[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]34.2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]40.77[/TD]
[TD="align: right"]40.92[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]41.6[/TD]
[TD="align: right"]37.22[/TD]
[TD="align: right"]36.23[/TD]
[TD="align: right"]43.43[/TD]
[TD="align: right"]0.1305[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]32.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]42.69[/TD]
[TD="align: right"]47.08[/TD]
[/TR]
[TR]
[TD="align: right"]41.8[/TD]
[TD="align: right"]43.5[/TD]
[TD="align: right"]36.21[/TD]
[TD="align: right"]37.86[/TD]
[TD="align: right"]47.79[/TD]
[TD="align: right"]0.133[/TD]
[TD="align: right"]0.54[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]28.6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]45.33[/TD]
[TD="align: right"]49.24[/TD]
[/TR]
</tbody>[/TABLE]

 

[smeador]

Also,

The table above shows almost no blanks until the bottom part of some columns. However, this is not the case for most of the data sets for players. Most of the time there are not any columns with data in each cell.

Thanks

 

[GooberTron]

Typically, the safest thing to do is exclude the entire row if it is missing a value for a given predictor. If you have lots of rows with a single different predictor missing - obviously this will reduce your dataset to virtually nothing. There are other statistical techniques for "filling the gaps", which are probably frowned upon to different degrees. You would find these options in standalone statistical environments, not in Excel.