How do I improve Accuracy of trendlines?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom21 - 01 Aug 2006 19:35 GMT

I am taking known data (4 points) and creating a trendling. I am selecting
the option to show the formula and R*R value, which is 1.0. I am charting
this equation and comparing it to the data used to create the formula, and to
the chart used to derive the formula. They are very different. Can I correct
this situation? If so, how? I have checked to make sure that I typed in the
correct formula. Where do I go from here?

Reply to this Message

Mike Middleton - 01 Aug 2006 20:26 GMT

Tom21 -

(1) Use more significant digits for your calculations. (A) Select the
trendline equation on the chart, and press the Increase Decimal button
repeatedly, or (B) use worksheet formulas, like INTERCEPT, SLOPE, or LINEST
for your calculations.

(2) Use an XY (Scatter) chart type. If you use a Line chart type, Excel uses
X values of 1,2,3,... for the calculations.

(3) Give us more information about the data, the chart type, and the
trendline Trend/Regression type you are using.

- Mike
www.mikemiddleton.com

>I am taking known data (4 points) and creating a trendling. I am selecting
> the option to show the formula and R*R value, which is 1.0. I am charting
[quoted text clipped - 5 lines]
> the
> correct formula. Where do I go from here?

Reply to this Message

Tom21 - 01 Aug 2006 21:17 GMT

Mike,

Thank you for your help. I was already using an XY Scatter chart type. I
increased the number of decimal places to 14. The equation got a little
messier, and the R*R value went down ever so slightly, but the created
function now matches the data.

> Tom21 -
>
[quoted text clipped - 21 lines]
> > the
> > correct formula. Where do I go from here?

Reply to this Message

Jerry W. Lewis - 01 Aug 2006 20:27 GMT

Right click on the equation and format to display scientific notation with 14
decimal places.

Also, make sure that your chart is an "XY (Scatter)" chart and not a "Line"
chart. The "Line" chart is very misleadingly named; it has nothing to do
with whether you want a line or not--rather it considers the x-data (if given
at all) to be category labels instead of numbers. Why it offers to fit a
trendline when it does not believe the x-data to be numeric is a mystery; but
when asked to do so, it assumes that the x-data are 1,2,3,... instead of the
values that you may have supplied. As a result, trendlines on "Line" charts
are usually worse than meaningless.

Jerry

> I am taking known data (4 points) and creating a trendling. I am selecting
> the option to show the formula and R*R value, which is 1.0. I am charting
> this equation and comparing it to the data used to create the formula, and to
> the chart used to derive the formula. They are very different. Can I correct
> this situation? If so, how? I have checked to make sure that I typed in the
> correct formula. Where do I go from here?

Reply to this Message