INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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.

Best to you,

Goober Dave

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

RE: Simple Linear Regression with Weights

In other words, you can reference it in another black box.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Simple Linear Regression with Weights

(OP)
Thanks all!

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

You stated in your original post that your bosses had imposed the (very odd to me) requirement that you solve the problem without using "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

Maybe he wants you to code the solution in assembler or machine code (1s and 0s)

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Simple Linear Regression with Weights

Ha. My instructor for assembler language, was a former Tandy Corp employee. He had some crazy stories about assembler geeks. But I guess that many of us could be classified as geeks in some universe. glasses

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Simple Linear Regression with Weights

Anyone else wanting to waste an hour or two might like to copy and paste the code from IRStuff's 2nd link and try and work out why it doesn't work.

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

It doesn't comply with the requirement for no "plug-ins" but the spreadsheet here:
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

I have fixed the code from http://stackoverflow.com/questions/19827547/progra... so it now returns a result.

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

(OP)
Thanks again all,

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

Have you considered Python? It's free, of course, and comes with oodles of crowd sourced code and add-ons. SPYder (they love the PYthon-related puns) has a GUI that's similar to Matlab's. The Python executables are available for the 3 major platforms: https://www.continuum.io/downloads (SPYder is part of the Anaconda distribution).

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

(OP)
Thanks for the tip, IRstuff.

Best to you,

Goober Dave

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

RE: Simple Linear Regression with Weights

Dave - did you try the VBA routine with weights not all equal to 1? I get different results from the Alglib results, as shown in my previous post. If you are able to post your data and typical results I'd be interested to have a look.

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

Doing a bit more research on this I have found:

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

That first link I posted uses weighting against the square. Seems then, the only thing that you need to do is the square the weights in the sheet.

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

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

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

magoo2 - Both functions use the sum of the squares of the errors, and give the same results if all the weights are 1. The difference is in how the weights are applied.

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

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

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!

RE: Simple Linear Regression with Weights

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

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?

RE: Simple Linear Regression with Weights

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

(OP)
You're a champion, Doug!

Best to you,

Goober Dave

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

RE: Simple Linear Regression with Weights

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.

RE: Simple Linear Regression with Weights

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: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close