Trendline Confidence Intervals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phil - 01 Mar 2004 05:58 GMT

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

Reply to this Message

Jerry W. Lewis - 02 Mar 2004 02:56 GMT

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

> This is probably a very simple problem to solve, but my
> statistical knowledge doesn't cover it, unfortunately.
[quoted text clipped - 15 lines]
>
> Phil

Reply to this Message

Phil - 02 Mar 2004 04:26 GMT

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

>-----Original Message-----
>Are you wanting a confidence interval for the line or a prediction
[quoted text clipped - 24 lines]
>
>.

Reply to this Message

Jerry W. Lewis - 02 Mar 2004 13:09 GMT

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

> Thanks for responding.
>
[quoted text clipped - 61 lines]
>>>
>>.

Reply to this Message