multiple nonlinear regression

[Lian]

Hello,

I had a question about using excel to calculate the regression
equation for many independent variables (I have 7). I'm not too
knowledgable about stats, but I used tools->data analysis-> regression
to get a multiple linear regression equation, but I want to try
polynomial or non-linear, as the R I got was a bit too low. I have no
idea what to expect in the resultant equation , so I don't want to
fill up the worksheet columns with A^2, A^3, B^2, C^3, etc...

I read the posts about LINEST, but when I tried it, all I get is a
number, when I want a list of coefficients or an equation, and when I
tried using ^{1,2,3}, I get a #VALUE! error.

Thank you any help...
Lian

 

[Michael R Middleton]

Lian -

I had a question about using excel to calculate the regression equation

for many independent variables (I have 7). I'm not too knowledgable about
stats, but I used tools->data analysis-> regression to get a multiple linear
regression equation, but I want to try polynomial or non-linear, as the R I
got was a bit too low. I have no idea what to expect in the resultant
equation , so I don't want to fill up the worksheet columns with A^2, A^3,
B^2, C^3, etc... <

I suggest that you first "look at the data." Plot Y versus each of the X
variables (a separate XY plot for each) to see if there are any obvious
nonlinear relationships.

The simplest way to handle many nonlinear relationships is to use both X and
X^2 as explanatory ("independent") variables. There is seldom a
justification for higher-order polynomials (and you may "overfit" the data).

So if you see some possible nonlinear relationships, add X^2 to your
existing data.

I read the posts about LINEST, but when I tried it, all I get is a number,

when I want a list of coefficients or an equation, <

The Regression tool uses LINEST, so there's no reason to use LINEST unless
you want the results to change dynamically when you change some of the X and
Y data values. If you do use LINEST, note that it must be "array entered."
That is, select a large range of cells (see Help for the size), enter the
LINEST function, and finish by pressing Control+Shift+Enter simultaneously
(instead of just pressing Enter).

and when I tried using ^{1,2,3}, I get a #VALUE! error. <

If you have 7 original X variables, that array exponentiation would yield 21
X variables for LINEST, and LINEST (and the Regression tool) is limited to
16 X variables.

- Mike

www.mikemiddleton.com

 

[Lian]

Hi,

Thank you for the suggestions! I'm going to go ahead and try the x^2
now... I remembered after plotting a couple of X-Y graphs for the
independent variables individually that most of my independent variables
had about 4 numbers that it varied between (except for one, that changes
with every 2).

Plotting just the Y stuff, though, gave me some interesting results so I
think I'll explore that further.

Once again, thank you for your help!
Lian