Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips now!
  • 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!

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

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

wcmzsp (Civil/Environmental) (OP)
9 Dec 10 11:59
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
Clyde38 (Electrical)
9 Dec 10 13:12
Check this and other similar threads:
http://www.eng-tips.com/viewthread.cfm?qid=184726

View Clyde's profile on LinkedIn

katmar (Chemical)
9 Dec 10 13:15
Fang, a very useful and powerful regression capability is already built into Excel. MicroSoft seem to enjoy making their help files as unhelpful as possible, and if you just type "regression" into the help search box it is unlikely you will find what you really want.  The trick is to type "Analysis Toolpak" into the help search box, and then all will be revealed.  In the default installation of Excel this Toolpak is not installed, but the Analysis Toolpak entry in the help file explains how to install and then use this function.

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

MechEng2005 (Mechanical)
14 Dec 10 14:14
First thing I notice is that in your example you have:

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
Denial (Structural)
14 Dec 10 16:12
Unless there is a typo floating around somewhere, Wcmzsp has actually asked for a form of hyperbolic regression.  Spacing it out to make it a bit clearer, he wants
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...)
 
MechEng2005 (Mechanical)
20 Dec 10 13:55
Oops. I misread the equation. Disregard my previous post. Sorry.

-- MechEng2005
BigInch (Petroleum)
17 Jan 11 8:35
Is this a hat trick?
Third time I posted this link in the last 3 minutes.
http://www.eng-tips.com/viewthread.cfm?qid=214997&page=1

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!

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