Trendline Confidence Intervals

[Phil]

This is probably a very simple problem to solve, but my
statistical knowledge doesn't cover it, unfortunately.

I am producing charts linking average car ownership to
average dwelling size for a large number of small
geographical areas. The charts are fairly scattered, but
Excel can fit is a reasonable straight trendline (r2 is
typically about 0.8).

As well as the best fit line, I want to add a line above
that shows best fit for 90% confidence - ie where only
10% of the points are above the line. How can this be
done, please?


Answers greatly appreciated.

Regards

Phil

 

[Jerry W. Lewis]

Are you wanting a confidence interval for the line or a prediction
interval for individual observations? Are you only interested in a
1-sided confidence bound?

Jerry

 

[Phil]

Thanks for responding.

I want a confidence interval for the line. The
individual scattered observations are survey data (actual
average number of cars, actual average size of dwellings,
for groups of around 120 dwellings), so these are fixed.

In theory I could plot confidence intervals on both sides
of the line, but the only one that matters is the upside -
i.e. a sensible upper bound, so that we could be 90%
confident (say) that car ownership was at or below the
line.

Hope this makes it clear!

Regards

Phil

 

[Jerry W. Lewis]

STEYX(y_fit,x_fit)*SQRT(1/COUNT(y_fit)+(x_conf-AVERAGE(x_fit))^2/DEVSQ(x_fit))*TINV(alpha,COUNT(y_fit)-2)

where alpha is suitably small (0.1 for 2-sided 90% confidence bounds,
0.2 for 1-sided 90% confidence bound).

x_fit and y_fit are the contiguous (no blank cells) data that was used
to fit the line.

x_conf is a point (or a range of points if you array-enter the formula)
where you want to compute the confidence bounds.

Note that over a range of x_fit's this is a curve, not a line.

Jerry