non-linear Multiple Regression

Question

I have several several inputs data (ranges from 4-7) and one output. The question is how I can produce a suitable equation connect these inputs (X1,X2 ...... X7) with output (Y) with low error.

Answer 1

Use worksheet formulas to transform the data. The multiple explanatory variables for the Regression tool must be in adjacent columns, so arrange X1^2, X2^3, sqrt(X3), e^X4, ... in adjacent columns. When you use the Regression tool, those columns are the X input range.

Refer to Tushar Mehta's section "Over-specifying a regression:" If you over-fit by using too many explanatory variables, you may get a high R square, but the model likely will not be useful for predictions.

-  Mike Middleton

TreePlan Software, http://www.TreePlan.com

Decision Tree, Sensitivity, and Simulation Add-Ins for Excel

Answer 2

Thanks. 

how to transfer data??

What I need is to connect my data as follows

a X1^2 + b X2^3 + c sqrt(X3) + d e^X4 ...... It is combination of polynomial and exponential and may be will be sin and cos ... The most important is to reach to R square near to 1.

Please advice

Answer 3

I have several several inputs data (ranges from 4-7) and one output. The question is how I can produce a suitable equation connect these inputs (X1,X2 ...... X7) with output (Y) with low error.

For examples on how to transform the data, see http://www.tushar-mehta.com/publish_train/data_analysis/16.htm in particular the section

Regression analysis with other trend functions

Answer 4

For multiple (linear) regression in Excel 2010, choose Data (ribbon) > (Analysis group) Data Analysis > Regression.

For non-linear relationships, transform the worksheet data first.

-  Mike Middleton

TreePlan Software, http://www.TreePlan.com

Decision Tree, Sensitivity, and Simulation Add-Ins for Excel