Displaying trend line equations on line charts

This post is prompted by a recent comment at Using LINEST for non-linear curve fitting which found that the trend line formula displayed on a chart was totally different from that found using the Linest function.

The problem was caused by using a line chart, rather than an XY (scatter) chart.  A line chart treats the x-axis values as text labels, even when the data range is formatted as numbers.  When calculating a trend line Excel treats the x range as a consecutive sequence of integers starting at 1, regardless of the value displayed.  As a result, if the x values are any other sequence the trend line equation displayed will be totally different to the correct one.

The solution is simple, convert the chart to an XY chart.

As an example, the screenshot below shows the function:
y = 2x^4 + 3x^3 – 4x^2 + 5x + 1
plotted on a line chart:

The trend line is a good fit to the plotted points, but the calculated trend line formula is totally different to the correct one.

The chart type can be changed to an XY (Scatter) type, using the  Chart-Tools, Design ribbon:

The function formula then displays correctly:

This entry was posted in Charts, Charts, Excel, Maths and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s