Simple Linear Regression with Weights
Simple Linear Regression with Weights
(OP)
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.
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





RE: Simple Linear Regression with Weights
However, the author implies that it's coded up in one of the toolboxes that he provides.
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Simple Linear Regression with Weights
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Simple Linear Regression with Weights
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Simple Linear Regression with Weights
IRstuff, that link hit the nail on the head for me. I have real-statistics add-in, but the function call for weighted linear regression is not documented well. However, I can use the real-statistics add-in to verify my work.
Best to you,
Goober Dave
Haven't see the forum policies? Do so now: Forum Policies
RE: Simple Linear Regression with Weights
However I won't tell your bosses if you won't.
RE: Simple Linear Regression with Weights
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Simple Linear Regression with Weights
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Simple Linear Regression with Weights
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Simple Linear Regression with Weights
Or if you want to not waste an hour or two, change:
Xtrans = Application.Tranpose(X)
to:
Xtrans = Application.Transpose(X)
i.e. insert an s before the p
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Simple Linear Regression with Weights
https://newtonexcelbach.wordpress.com/2011/02/17/a...
will do weighted linear regression, using the Alglib library.
The Alglib version in that link is all VBA and open-source, so you could combine the code with your own, if that's really necessary.
But if the purpose is a cross-check of statistical software, it should do the job, as it is.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Simple Linear Regression with Weights
If all the weights are 1 it gives the same results as my Alglib spreadsheet, but with different weights it gives different results (and I believe the Alglib results are correct).
I have attached the wls function spreadsheet, and the Alglib spreadsheet results are shown in the screenshot below:
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Simple Linear Regression with Weights
I was able to un-function the VBA code from Doug to please the boss. It was a bit hairy, but it gives the same results as real-statistics (I didn't try ALGLIB).
What a hassle it's been - but the boss is the boss, and he wants something that can be easily translated to a different programming language later on. For now, we're stuck with Excel and VBA --
My only wish now is that Excel and VBA knew how to use all the cores in my computer. Dang program takes five minutes to run.
Best wishes all!
Best to you,
Goober Dave
Haven't see the forum policies? Do so now: Forum Policies
RE: Simple Linear Regression with Weights
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Simple Linear Regression with Weights
Best to you,
Goober Dave
Haven't see the forum policies? Do so now: Forum Policies
RE: Simple Linear Regression with Weights
If you do decide to look at Python you might like a look at:
https://newtonexcelbach.wordpress.com/2016/03/20/x...
which links Excel to the Python Scipy library, including weighted curve fitting routines.
Regarding solution time, pure Python can be quite slow (even slower than VBA), but Scipy includes some very fast linear-algebra routines, so I'd expect the curve fitting to solve in seconds rather than minutes.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Simple Linear Regression with Weights
The VBA routine will give the same results as the Alglib routine if the weights are squared. It seems that the VBA applies the weight to the errors, whereas Alglib applies them to the square of the errors. I have no idea which is correct.
The most convenient Python function for least-squares fitting is in the Numpy library (numpy.linalg.lstsq(a, b, rcond=-1)). This is not currently included in my XlScipy spreadsheet (but I will add it). This function does not provide for weights, but a simple procedure to add them is given at:
http://stackoverflow.com/questions/27128688/how-to...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Simple Linear Regression with Weights
Ostensibly, it makes more sense that the weights apply to the RSS, which requires the squares.
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Simple Linear Regression with Weights
I think from my Six Sigma days the curve fit is usually based on the sum of the square of the errors. If you used the error rather than the error-squared, you could have one data point that is well below the curve fit and everything else is above the fit and the sum would be close to zero. By using the square of the error approach, both errors above and below the line are evaluated in the sum. So I think the square approach is a fairer method. That's my personal opinion.
RE: Simple Linear Regression with Weights
The Wikipedia article:
https://en.wikipedia.org/wiki/Linear_least_squares...
suggests that the VBA results are correct. Also I have now checked with the Python function, and it also gives the same result as the VBA, rather than the Alglib.
By the way, the Scipy least squares fnction is buried in my Scipy spreadsheet (on the Linalg page), but it doesn't allow for weighting. I will post a version with weights in the next few days.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Simple Linear Regression with Weights
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: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Simple Linear Regression with Weights
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!
RE: Simple Linear Regression with Weights
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Simple Linear Regression with Weights
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?
RE: Simple Linear Regression with Weights
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.p...).
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/
RE: Simple Linear Regression with Weights
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Simple Linear Regression with Weights
https://newtonexcelbach.wordpress.com/2017/02/05/w...
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.xls...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Simple Linear Regression with Weights
Best to you,
Goober Dave
Haven't see the forum policies? Do so now: Forum Policies
RE: Simple Linear Regression with Weights
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.
RE: Simple Linear Regression with Weights
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm