Weighting linear regression? Math/Excel question?

[SHorse]

Hi all,

Quick question from someone with no formal statistical training trying to make their way in Excel...

I'm trying to throw together a linear regression correlating a specific value for a lineup with that lineup's ORtg, working to put together a predictive model/slope for the regression line, but I'm currently treating every lineup with the same weight, which makes the data a little noisy. Ideally, I'd want to be able to weight each lineup by the amount of time and/or possessions they actually played, giving the items with a larger sample size greater heft in the data. I've struggled with this idea for awhile, but haven't had much luck so far - and the research side has gotten me into formal math fairly quickly, where I'm just a little bit out of my depth.

Currently I'm just using the straight LINEST function in Excel. Anyone have any recommendations?

Much obliged in advance!
___________________________________________________________________________________
SHorse

[EvanZ]

It may be easier to do that in R, where weighted linear regression is very simple.

Not sure how to do that in Excel.

[SHorse]

I was afraid of that. I must confess to opening R and being instantly bewildered by the console - even the help page. If there's no simpler way, I guess I can export my current data into a .csv file or something similar and start experimenting...?

[EvanZ]

I was afraid of that. I must confess to opening R and being instantly bewildered by the console - even the help page. If there's no simpler way, I guess I can export my current data into a .csv file or something similar and start experimenting...?

There are others here who use Excel a lot more than I do, so it might be possible. Did you do a Google search for "Excel weighted linear regression"?

[ed küpfer]

I was afraid of that. I must confess to opening R and being instantly bewildered by the console - even the help page. If there's no simpler way, I guess I can export my current data into a .csv file or something similar and start experimenting...?

I've seen add ins that claim to do more complex regressions in excel, but I've never heard of anybody that actually used one.

My answer is probably not the one you want to hear, but I'll put it out there anyway: the effort that you put into finding an excel solution would probably be better spent learning how to do it in R. Once you have the basic data import-export functions down in R, you can focus on the regression functions, which may be a little complicated, but are generalisable -- the other thing excel doesn't do is logistic regression (for binary response variables, like made/missed shots), but is a piece of cake in R once you know the simple linear regression lm() function. If you want to try, here is a tutorial on the basics.

Even if you're overwhelmed at the prospect of learning R, at the very least you should look into some kind of dedicated stats package -- before I learned R, I used Minitab, which was simple to use.

[SHorse]

Thanks, both of you - the advice definitely rings true, as immediately unwelcome as it may be. I'm fighting multiple steep learning curves right now, but that's half the fun, right? Or maybe 1/3rd of it; we should try to figure that out sometime.

Thanks in particular for the web resource - I hope to have something functional and worthwhile to show for this in the next little while, I'll be sure to post here if it's presentable enough...
____________________
Shorse

[bbstats]

This is a great resource for learning regression in R -- and it involved NBA lineups

Excel will die if you try and use lots of data or lots of parameters.

[SHorse]

Much obliged!
_______________________________
SHorse