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!

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

<tbody>
</tbody>

 

[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.