Linear Predictive Equation

[Guest]

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

 

[Mike Middleton]

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

 

[Guest]

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.

 

[Guest]

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/d6a03470e7a1c650
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