Inverse prediction in regression

[Ernie Lippert]

After performing a linear or quadratic regression of y = f(x), I need to
find the find the Lower and Upper bounds for the 100gamma percent confidence
interval for the predicted value of x given one or more observed values of
y.
Regards,
Ernie

 

[Jerry W. Lewis]

Excel has no built-in functions to calculate the standard error of a
predicted value, much less do the inversion.

LINEST will give you the standard erorr of coefficients, but not their
covariance. For simple linear regression, the covariance between slope and
intercept estimates is
-AVERAGE(xdata)/SQRT(VAR(xdata)+AVERAGE(xdata))
Standard error of a predicted value at xx (possibly not a value in the
original xdata) is then
SQRT(1/n+(xx-AVERAGE(xdata)/DEVSQ(xdata))
You form a 95% confidence interval for the predicted value by multiplying by
TINV(.05,n-2)
To invert these confidence limits, you solve quadratics where the upper and
lower and lower confidence bounds equal your observed y.

Jerry

 

[Mohit Bansal]

Hi Jerry

I am trying to calculate the standard error of a prredicted value of x at 75% y after fitting linear regression.

I came across the equation below, but it is missing one of the brackets.

Standard error of a predicted value at xx (possibly not a value in the original xdata) is then
SQRT(1/n+(xx-AVERAGE(xdata)/DEVSQ(xdata))

I believe it needs to be before DEVSQ, but I am not sure. Can you please confirm this equation for me?

Thanks in advance
Mohit

 

[IanC]

I can't be sure as I'm not sure what you're trying to achieve, but as there
are 4 instances of "(" and only 3 of ")" you need to add another ")" or
remove a "(".

I suspect you need the add another ")" at the end.

This would make sense when you break down the formula
SQRT(X)
where X is
1/n+(Y/Z)
where Y is
xx-AVERAGE(xdata)
and Z is
DEVSQ(xdata)

Therefore your final formula would be
SQRT(1/n+(xx-AVERAGE(xdata)/DEVSQ(xdata)))