More on trend line equations on line charts

In the previous post we saw that if a trend line equation is added to a line chart it will return the wrong coefficients if the x values are not a continuous sequence of integers, starting at 1 (click on any image for full size view):

Using an XY chart in place of the line chart is one way to get the correct trend line (which will work for any sequence of x values), but there is another way:

Right click on the X axis, and select Format Axis … then set Axis Type to Date axis:

The line chart and the XY chart now display the same (correct) trend line.

The trend line will also still work correctly if there are one or more negative x values (although the values do not display in the axis labels):

But be careful, the line chart treats the x values as being date numbers, and truncates all decimal values to the integer part:

For more details see Jon Peltier’s blog.

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

One Response to More on trend line equations on line charts

  1. Pingback: #Excel Super Links #20 – shared by David Hager | Excel For You

Leave a Reply

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

You are commenting using your 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