Multivariable regression in Excell
Multivariable regression in Excell
(OP)
Does anyone have any suggestions how to do a multivariable and power regressions in Excel and calculate the coefficients and some statistical characteristics of the fitted equation?
For example: y=a*x1+b*x1*x2^c
Here, x1, x2 are the independent variables; y is the dependent variable; a, b and c are coefficients.
Any suggestions for this one?
Thank you,
Fang
For example: y=a*x1+b*x1*x2^c
Here, x1, x2 are the independent variables; y is the dependent variable; a, b and c are coefficients.
Any suggestions for this one?
Thank you,
Fang





RE: Multivariable regression in Excell
http://www.eng-tips.com/viewthread.cfm?qid=184726
RE: Multivariable regression in Excell
A very nice feature of Excel is that you can then set up a table to use the calculated regression coefficients to see how well the curve fits the data - especially if you make a scatter chart of the data. As soon as you calculate the coefficients the graph is updated and gives you a very clear idea of how good the fit is. As you, for example, change the degree of the fitting polynomial you can see how the fit improves (or not!).
The help file (as I remember it) talks about multi-variable regression. For polynomials just use x, x^2, x^3 etc as the different variables.
Katmar Software
Engineering & Risk Analysis Software
http://katmarsoftware.com
RE: Multivariable regression in Excell
a*x1 + b*x1
This is the same as (a+b)*x1, so without more information the best you can do is find (a+b), but not an exact value for a or b.
For example, say a+b = 10. Your function will be the same for:
a=1, b=9
a=10, b=0
a=5.23, b=4.77
There is no way to determine which values of a and b are correct for the equation you provide.
So, step one is to determine if you NEED to know both a and b, or if just knowing the sum of a and b is acceptable. If you need a single solution for a or b, you need more information than just the equation you provided.
-- MechEng2005
RE: Multivariable regression in Excell
Y = a*x1 + b*x1*(x2)^c
This cannot be handled by Excel's inbuilt regression capabilities. However the values of the coefficients a, b and c can be estimated by converting the problem into a minimisation exercise and using the Solver to achieve the minimisation. The approach is spelled out / discussed (albeit in a different context) in the following two posts
thread770-82805: Higher order polynomial trendlines in Excel
thread770-231694: Excel Trend Line
However this approach will not give you the statistical characteristics of the resulting regression. I cannot offer any suggestions for those. (But I'm all ears...)
RE: Multivariable regression in Excell
-- MechEng2005
RE: Multivariable regression in Excell
Third time I posted this link in the last 3 minutes.
http://www