Thread: How do I improve Accuracy of trendlines?

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?

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

"Tom21" <Tom21@discussions.microsoft.com> wrote in message
news:2A2C8E72-0448-49F1-B576-03778D10D9B1@microsoft.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
> 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?

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

"Tom21" wrote:

> 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?

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.

"Mike Middleton" wrote:

> 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
>
> "Tom21" <Tom21@discussions.microsoft.com> wrote in message
> news:2A2C8E72-0448-49F1-B576-03778D10D9B1@microsoft.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
> > 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?
>
>
>