## 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

ANY[my emphasis]built-in Excel functions or plug-ins". I hate to rain on your parade, but the solution you seem to have latched onto does not meet that requirement. It makes multiple use of Excel's Transpose() and MMult() functions, even if it does hide them away in the VBA.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

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

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

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

## 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

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

## 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

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

## 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

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

## 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

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg