regression

[AudreyJ]

Does anyone know how to predict a value from multiple dependant variables? I
know how to run the data analysis tool to get the "residual output" but
that's it. Also, I have variables which are drivers which are not numbers,
they are characteristics like "region", like US, Japan or Europe. Does
anyone know how I can account for these?

In the "excel help", they say you can do the following:

For example, you can analyze how an athlete's performance is affected by
such factors as age, height, and weight. You can apportion shares in the
performance measure to each of these three factors, based on a set of
performance data, and then use the results to predict the performance of a
new, untested athlete.

I basically need to know how to do that.

Any help would be greatly appreciated I am very stressed here trying to
figure this out!

- Audrey

 

[Mike Middleton]

AudreyJ -

One way to make predictions using the Regression tool output is to create a
formula in a cell that refers to your new X values and to the Coefficients
in the output. If your formula uses cell references to the Coefficients,
you'll take advantage of Excel's fifteen significant digits and thereby
avoid rounding errors.

- Mike
http://www.mikemiddleton.com

 

[AudreyJ]

Thanks Mike!

Would you mind helping me out with the formula to do so? I'm not sure how
to handle multiple coefficients. For example, below are the multiple
coefficients for my intercept, I'm not sure how to predict an x value
formulaically from the output.

Intercept 262.82
Expected Loss (bps) 3.26
Notional (mm) 2.13

Also, as I talked about, I have many driver variables which are not numeric
such as geographical region, do you have any insight on how I could go about
handeling those in the analysis?

Thanks so much again for your response!

- Audrey

 

[Mike Middleton]

AudreyJ -

Standard form is y = b0 + b1*x1 + b2*x2, where b0 is the intercept and b1
and b2 are the other coefficients.

Those coefficients might appear in cells B17:B19 of the Regression output.
If you want a prediction of y for x1 = 23 and x2 = 57, select an unused
cell, and enter =B17+B18*23+B19*57.

Regarding categorical variables (geographical regions), use indicator
variables. For a very recent discussion, browse to google.groups.com and
search for "excel regression data not numerical" (without the quotes).

For considerably more explanation for both topics, consult modern business
statistics textbooks that use Excel, or consult my inexpensive paperback
"Data Analysis Using Microsoft Excel: Updated for Office XP," which has six
or seven chapters on regression.

- Mike
http://www.mikemiddleton.com

 

[AudreyJ]

Thank you Mike!

AudreyJ -

Standard form is y = b0 + b1*x1 + b2*x2, where b0 is the intercept and b1
and b2 are the other coefficients.

Those coefficients might appear in cells B17:B19 of the Regression output.
If you want a prediction of y for x1 = 23 and x2 = 57, select an unused
cell, and enter =B17+B18*23+B19*57.

Regarding categorical variables (geographical regions), use indicator
variables. For a very recent discussion, browse to google.groups.com and
search for "excel regression data not numerical" (without the quotes).

For considerably more explanation for both topics, consult modern business
statistics textbooks that use Excel, or consult my inexpensive paperback
"Data Analysis Using Microsoft Excel: Updated for Office XP," which has six
or seven chapters on regression.

- Mike
http://www.mikemiddleton.com