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

Excel Regression Multivariable

Excel Regression Multivariable

(OP)
Hi,

I'm working in a worksheet that calculates all the regression types, linear and non-linear equations. But I'm using more than two independent variables(eg. X1,X2,X3,X4).I'm using the Excel formula =LINEST(), but I don't know how to introduces the information on "Knows_x's".



For the Multi variable Linear Regression I uses the following: =LINEST(Y,X1:X4,TRUE,TRUE) and I get the correct information. In the case of the Quadratic I don't know how to calculate. I found the following solution =LINEST(Y_1,X_1^{1,2}); but I don't know how to do it and I need to make this calculation with the Cubic,Power and Log regression.

I will appreciate your support on this.

Best Regards.

RE: Excel Regression Multivariable

Do the squaring (cubing, logging, etc-ing) yourself, and put the results into the table of independent variables that you feed into LINEST().

Or am I missing something?

RE: Excel Regression Multivariable

You can generate the values for a non-linear regression on the spreadsheet, but you don't need to.

See Using LINEST for non-linear curve fitting
which has a spreadsheet for download with examples of all the available non-linear curve types.

If anything isn't clear, please ask.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Excel Regression Multivariable

(OP)
Denial: Thank you and think that could be a option, but I would like to make more quickly.

IDS: Thank you. Actually I'm using that spreadsheet, but it has only one X's and that's the reason that I need to know how to do it with more that 2 X's.

RE: Excel Regression Multivariable

If you want to get a regression for (for example) y = aX_1 + bX_1^2 + cX_2 +dX_2^2 + e then denial's way is probably the simplest way to do it.

When you have a single X variable, say in A1:A10, then the formula (A1:A10)^{1,2} returns a two column range with x in the first column and x^2 in the second. You can use this in the regression formula, or enter it directly on the spreadsheet (using ctrl-shift-enter), and use that range in the regression formula.

I was about to say that this doesn't work with a multi-column range for x, but you can actually get it to work. If you have say X_1, X_2 and X_3 in range A1:C10, then copy that range to D1:F10, then you can use: =LINEST(Y_Range,A1:F10^{1,1,1,2,2,2}). It would be good if A1:C10^{1,1,1,2,2,2} returned a 6 column range, in the same way that (A1:A10)^{1,2} returns a two column range, but it seems it doesn't work that way.

There may be another trick to get this to work without any copying of data, but a quick search didn't find anything.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

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