Simple Linear Regression with Weights 7

[DRWeig]

Group,

Does one of you perhaps have a VBA routine for linear regression with weighting, without using any built-in Excel functions or plug-ins (boss request).

I've attached a very simple example of data. My program is much more complex, but I've never dealt with weighted regressions before without using a "black box" plug-in for Excel.

If you have something, or can do it without much trouble, I'd be most appreciative.

Best to you,

Goober Dave

Haven't see the forum policies? Do so now: Forum Policies

 

[IRstuff]

Doug,

I might not be understanding you

The last part results in W*(y-X*B){sup]2[/sup], so the square of deltas are weighted, not the deltas.

Since the squared deltas are more heavily moved by large deltas, it makes sense to beat them down directly, and to boost squares of small deltas directly

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[magoo2]

IDS- sorry I missed your point. Errors are squared and its just an issue of whether you square the weighting factor or not. I understand now.

After two beers, it seems like a better approach for regression analysis would be to work with the absolute value of the error and apply a weighting factor or a weighting factor-squared - even though this probably goes against most statistical recommendations!

 

[IRstuff]

I think that's the way it should be. The regression is based on what's called "Least Squared Error" because the squared errors all wind up positive, and the larger errors get beat down before the smaller errors, which is the way things should work.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Denial]

Doug/IDS and others.
On the side topic of how to apply the weighting, ie to the error or the squared error.
What follows is "straight off the top of my head" (or maybe straight out of a more southerly orifice).

It is going to depend upon what a person means by the word "weighting".[ ] I doubt there is a widely agreed definition among statisticians.[ ] To my way of thinking, if sample point A has a weighting of twice that of sample point B, then A is worth twice as much as B.[ ] This would mean that in an unweighted analysis you would enter point A twice and point B only once.[ ] In a weighted regression based on minimising the sum of the squared errors the equivalent of this is to apply the weighting to the error's square.[ ] In a weighted regression based on minimising the sum of the magnitudes of the errors the equivalent would be to apply the weighting to the error's magnitude.

It there a statistical philosopher or a philosophical statistician in the house?

 

[IDS]

Denial - having worked through some examples, I'd say:

The weighting is applied to the square of the errors in all the code discussed in this thread, but the Alglib code also squares the weights (see Note 4 at:

http://www.alglib.net/interpolation/leastsquares.php#header1).

So Alglib minimises Sum((w * error)^2)
whereas the VBA and Python functions minimise Sum(w * error^2), which is also what Wikipedia says should be done. Dave also said his "real statistics" package gave the same results as the VBA. So it seems that the Alglib code is the odd one out.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

Well, that seems to make more sense.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[IDS]

I have now written this up as a blog post, including downloads of the VBA, Alglib and Python functions:

https://newtonexcelbach.wordpress.c...regression-using-excel-vba-alglib-and-python/

I have tidied up the VBA code in the WeightLSq.xlsb spreadsheet, so it should run much quicker now:

http://interactiveds.com.au/software/WeightLSq.xlsb

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[DRWeig]

You're a champion, Doug!

Best to you,

Goober Dave

Haven't see the forum policies? Do so now: Forum Policies

 

[Denial]

Doug.[ ] Sorry for the delay in replying:[ ] too many things coming to the boil at once.[ ] I agree with everything you say in your 05Feb17@04:46 post.

However a test of my philosophical musing would be to find a (trusted) web site that discusses the application of weightings to data fitting that minimises total absolute error rather than total squared error.[ ] I have not been able to find one.

 

[IRstuff]

But, linear regression does, in fact, use total squared error. Given that, the actual weighting seems a bit arbitrary, since it's now a subjective calculation, in that the user has "decided" that some points are more important to fit than others. I've been down that route before, and there's really no objective way to set the weights, as there are now essentially two additional degrees of freedom, the points to be weighted, and the amounts.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm