Forum Discussion
Chart displaying "Trendline Error"
- SergeiBaklanJul 22, 2020Diamond Contributor
RenTheron , seiscons - is it possible to submit small sample file and on which version of Excel you are?
- seisconsJul 24, 2020Copper Contributor
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.
- seisconsJul 24, 2020Copper Contributor
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:
- seisconsJul 23, 2020Copper ContributorOffice 365. i.e. 2016 with the latest updates. I'll put together a simple sample file and upload it.