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.