• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2405
  • Last Modified:

Logic behind trend line graphs in excel

Hi,

We created a trend line graph in excel (no problems there).  However, we were posed with a question that seemed like it should be easy, but we were stumped.

How does excel determine what the trend is?  What is the logic behind what the graph does?  I couldn't find the info in the few books we have on excel or a web search (though a link could be out there somewhere).

If you have any ideas or know the answer, it would be appreciated.

Thanks,

Jay
0
dpimcadmin
Asked:
dpimcadmin
1 Solution
 
pauloaguiaCommented:
Trendlines are an approximation of the line in the chart. You can have different types of trendlines, each one better for a certain type of curve. One good way to start is by calling the Office Assistant (aka Clip-it) and typing trendline in the answer wizard. Then choose the Topic "Choosing the best trendline for your data". There you'll find an brief explanation on what curves each trendline is better for.

Basically Excel grabs all your series points and tries to adjust a function (could be a linear function, exponencial, etc) to the points. As all functions, the generic functions have parameters. Excel adjusts these parameters so that the error between the chart series and the trendline is the lowest possible.

Once the function is found you can use that function to predict other values not in the chart (values into the future for instance). The closer the trendline adjust your chart, the better this prediction will be.

Hope this helps

Paulo
0
 
Dave BrettVice President - Business EvaluationCommented:
Jay,

Trendlines are used to

1) Interpolate (data that lies withing the range) or extrapolate (data that lies outside the range)
2) Confirm if a trend (weak or strong) exists in the data

For example if you graph some of the points from the equation

Y = Sin(x)

And fit a polynomial trend, excel runs a minimisation routine called least squares to fit the best possible curve. Note, Sin(x)can be perfectly fitted by a Taykor polynomnial

The least squares approach penalises the trendline accuracy (R2 value)if the actual data is signifcantly different from the trendline prediction. It is a better fit if three data preidctions are slightly wrong than if 1 data prediction is strongly wrong.

A R2 value of 1.0 means the all the data predictions lie on the actual curve, a R2 of 0.75 means that 75% of data can be explained by the trend and 25% is erroroneous.

see http://standards.nctm.org/document/eexamples/chap7/7.4/

Cheers
Dave
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now