Linear Predictive Equation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linda - 22 Jun 2006 02:56 GMT

Does anyone know how to develop a linear predictive equation for one set of
variables based on using a different independent variabe?

Reply to this Message

Mike Middleton - 22 Jun 2006 03:55 GMT

Linda -

For a single explanatory variable, use (1) an XY (Scatter) chart and Add
Trendline or (2) the INTERCEPT and SLOPE worksheet functions.

For one or more explanatory variables, use (3) the Regression tool of the
Analysis ToolPak or (4) the array-entered LINEST worksheet function.

- Mike
www.mikemiddleton.com

> Does anyone know how to develop a linear predictive equation for one set
> of
> variables based on using a different independent variabe?

Reply to this Message

Linda - 23 Jun 2006 01:53 GMT

Thanks! You really helped a lot. Now I need to know how to develop a
confidence interval based on that equation based on a certain independent
variable.

> Linda -
>
[quoted text clipped - 10 lines]
> > of
> > variables based on using a different independent variabe?

Reply to this Message

Jerry W. Lewis - 23 Jun 2006 03:05 GMT

If you have an earlier version than Excel 2003, you may prefer the formulas at
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/d6
a03470e7a1c650
Similarly, the numerically best way to calculate yhat, the predicted value
at x is
yhat = AVERAGE(ydata) +(x-AVERAGE(xdata))*SLOPE(ydata,xdata)
instead of FORECAST, or using the intercept estimate.

Regardless, the confidence bound at x (not necessarily an observed value)
for the regression line is
yhat +/- TINV(1-conf,df) *STEYX(ydata,xdata)
*SQRT(1/n+(x-AVERAGE(xdata))^2/DEVSQ(xdata))

Jerry

> Thanks! You really helped a lot. Now I need to know how to develop a
> confidence interval based on that equation based on a certain independent
[quoted text clipped - 14 lines]
> > > of
> > > variables based on using a different independent variabe?

Reply to this Message