Jul 22 2020 10:39 AM
Jul 22 2020 10:39 AM
I have charts which plot linear data ranges with trendlines using named ranges and OFFSET to make the ranges dynamic. Sometimes the chart series data range is a pair of cells containing zeros. In the past this was not a problem; the point in those chart ranges just plot at 0,0 on the chart. Recently, however Excel also displays the word Trendline Error (presumably because there is only one data pair:
This is terrible. The plotting of the points and data values of 0 at the origin isn't too bad but I do not want the words Trendline Error displayed just because there is only a single data pair.
This is a recent problem. I have been using this technique for years and now the workbooks that have some chart ranges with empty cells do this. Below is what happens if there are two charge ranges like this.
Is there any way of switching this error display off?
Jul 23 2020 08:08 AM
Jul 23 2020 10:54 PM
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).
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.
Jul 23 2020 11:37 PM
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:
Jul 24 2020 06:33 AM
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.
Jul 24 2020 06:58 AM
Jul 24 2020 07:41 AM
Feb 09 2021 06:21 AM
@seiscons This has popped up in a regular report that I update frequently. I have narrowed it down to one series that has three negative numbers in it. If I make them positive, the error goes away. Super frustrating. It was not happening in that file before 11/17/2020.
Feb 10 2021 07:50 PM
I am surprised to read that the problem is still occurring. A while back the problem went away for me (Excel 365) and all of the workbooks that exhibited the problem started to work properly. I did report it by posting a "Comment" to Microsoft and the problem went away a few weeks later. I thought that they had fixed it.