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?

How do I improve Accuracy of trendlines?
 

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" wrote in message
...
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?

How do I improve Accuracy of trendlines?
 

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?

How do I improve Accuracy of trendlines?
 

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" wrote in message
...
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?