Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

Simple Linear Regression with Weights 7

Status
Not open for further replies.
DRWeig

DRWeig

Electrical
Apr 8, 2002
3,004
0
0
US
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
 
 http://files.engineering.com/getfile.aspx?folder=ff54b63e-9fda-4685-9f88-e89e5cb1cf3f&file=Simple_Weighted_Linear_Regression.xlsx
Replies continue below

Recommended for you

Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Maybe he wants you to code the solution in assembler or machine code (1s and 0s)

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
It doesn't comply with the requirement for no "plug-ins" but the spreadsheet here:

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
 
Upvote 0 Downvote
I have fixed the code from 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:

URL]


Doug Jenkins
Interactive Design Services
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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: (SPYder is part of the Anaconda distribution).

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list
 
Upvote 0 Downvote
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:

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
 
Upvote 0 Downvote
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:

Doug Jenkins
Interactive Design Services
 
Upvote 0 Downvote
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! faq731-376 forum1529 Entire Forum list
 
Upvote 0 Downvote
"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.
 
Upvote 0 Downvote
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:
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

vinodnx7
  • Locked
  • Question
count the same text from the file and create new list with number
Replies
19
Views
21
vinodnx7
vinodnx7
Tuckabag
  • Locked
  • Question
Acessing CMD shell in Excel/VBA with keystrokes and copying resultant data 1
Replies
8
Views
37
3DDave
3DDave
skeletron
  • Locked
  • Question
Footers and Page Break boundaries with print-to-PDF
Replies
3
Views
7
3DDave
3DDave
VH88
  • Locked
  • Question
File System Object won't recognize file name or file path with space | File Not Found Error
Replies
5
Views
7
dik
dik
xMeck
  • Locked
  • Question
Confused with a simple excel/graph issue 3
Replies
10
Views
11
BigH
BigH
Back
Top