Forum Discussion
Chart displaying "Trendline Error"
I have had a closer look and it is clear that the problem occurs when a Chart Series with a Linear Trendline plots data that is all zeros. The attached workbook illustrates this. The first chart in the workbook plots data from the table in two ranges with the first Chart Series plotting data from rows 2 to 4 and the second Chart Series plots data from rows 4 to 6. (The second chart in the workbook shows all the data plotted with a single series). So:
this data plots like this >
If I then ender a zero into all the cells for row 4 to 6 the words "Trendline Error" appears over the top of the left axis (and the second series all plots at 0,0):
this is what happens>
Then if I then undo these changes (the 0 entries), the trendline formula appears attached to the second Chart Range trendline as one of the other contributors mentioned (the formula can be deleted - but not on a protected workbook of course).
then produces>
So the problem occurs when the data for a Chart Range is all zero.
I appreciate your interest, Sergei, as this is a serious problem for us because this occurs in workbooks that are used in a professional setting. The workbooks are protected and the charts produced are provided to clients and included in technical reports. To me it looks like this behavior is the result of a recent Excel update as it only just started happening.
An additional note: The problem occurs when all of the X values of the second Chart Series are zero:
It doesn't happen if the Y values are zero:
And with a chart with only one series. If all the X values are 0 it happens and the trendline doesn't plot but it is OK if all the Y values are zero and then the trendline plots OK:
If all X values are the same, you get a trendline error because the slope is infinite. If all Y values are the same, there is no error, because the trendline is horizontal. This does not explain why the trendline error message appears.
I have not encountered this issue, but I can reproduce it easily enough in Microsoft 365. I'd have to boot up an older laptop to see if it occurred in an earlier version of Excel (2016).
If I was stuck with this problem, I'd calculate my own trendline data in the worksheet, using FORECAST.LINEAR or even SLOPE and INTERCEPT, and plot this data in my charts.
- Thanks Jon, that makes sense about the X and Y difference. I could create trendlines as you say, but I use the technique in several production workbooks that are set up for personnel to use and it would be a lot of work modifying the workbooks and re-deploying the templates. I think I'd prefer Microsoft fix the issue as it really does appear to be something new. I guess I'll have to if they don't.
I see now, thank you for so many details. I didn't see such behaviour before but can reproduce on different versions of Excel 365. Will try to check if it is possible to avoid error message and equation.
