Solved

# Logic behind trend line graphs in excel

Posted on 2003-03-12
Medium Priority
2,358 Views
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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 9

Expert Comment

ID: 8123151
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

LVL 50

Accepted Solution

Dave Brett earned 1200 total points
ID: 8124174
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
###### Suggested Courses
Course of the Month15 days, 6 hours left to enroll