Chart displaying "Trendline Error"

Copper Contributor

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:

seiscons_1-1595438857067.png

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.

seiscons_2-1595439018183.png

Is there any way of switching this error display off?

 

 

24 Replies
having same issue. leaving this here to see if some finds solution

@RenTheron , @seiscons - is it possible to submit small sample file and on which version of Excel you are?

@seiscons I'm experiencing the same thing all of a sudden - I noticed it 7/20.  Not only do I see the error message, but all of the trendline equations somehow are now turned on (I had them off before). 

Office 365. i.e. 2016 with the latest updates. I'll put together a simple sample file and upload it.
I noticed the equations but assumed I must have displayed them. Thanks for that. There is something definitely going on here and I suspect it is a result of a recent update. Are you also using Excel 2016 and updating it regularly, Bonnie?

@seiscons Yes, I'm using Office 365 and getting regularly updated.

@RenTheron 

 

Same issue as well, just started happening, even when i open old excel spreadsheets trendline error appears

@Sergei Baklan ,

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:

seiscons_0-1595568503014.png  this data plots like this > seiscons_1-1595568547466.png

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):

seiscons_3-1595568673024.png this is what happens> seiscons_5-1595568788437.png

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). 

seiscons_6-1595568874112.png then produces> seiscons_7-1595568920187.png

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:

seiscons_0-1595572135363.png

It doesn't happen if the Y values are zero:

seiscons_1-1595572234673.png

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:

seiscons_2-1595572435954.png

seiscons_4-1595572625720.png

 

 

 

 

 

@seiscons 

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.

@seiscons 

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.

Create Excel charts with trendline, error bars, data tables, data labels in C# using Spire.XLS

Tools free us from extra work. Here I use .dll from free Spire.XLS to create Excel charts and add all the chart tools I mentioned above if you would like to try, you could get it from E-iceblue official website or Nuget.

Step 1: I use the following code to create a column clustered chart (see the screenshot) with trendline and data table using the .dll in Visual Studio.

Screenshot: (R2=0.9688, which means the trendline is very fit to the data.)


Figure 4: Chart

Code
//Create an Excel and add a sheet
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];

//Fill Excel cells with sample data
sheet.Name = "sample 1";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan.";
sheet.Range["A3"].Value = "Feb.";
sheet.Range["A4"].Value = "Mar.";
sheet.Range["A5"].Value = "Apr.";
sheet.Range["A6"].Value = "May.";
sheet.Range["A7"].Value = "Jun.";
sheet.Range["B1"].Value = "Porter";
sheet.Range["B2"].NumberValue = 15;
sheet.Range["B3"].NumberValue = 18;
sheet.Range["B4"].NumberValue = 24;
sheet.Range["B5"].NumberValue = 32;
sheet.Range["B6"].NumberValue = 37;
sheet.Range["B7"].NumberValue = 40;
sheet.Range["C1"].Value = "Harry";
sheet.Range["C2"].NumberValue = 38;
sheet.Range["C3"].NumberValue = 32;
sheet.Range["C4"].NumberValue = 17;
sheet.Range["C5"].NumberValue = 35;
sheet.Range["C6"].NumberValue = 45;
sheet.Range["C7"].NumberValue = 43;
sheet.Range["D1"].Value = "Chocolate";
sheet.Range["D2"].NumberValue = 30;
sheet.Range["D3"].NumberValue = 28;
sheet.Range["D4"].NumberValue = 35;
sheet.Range["D5"].NumberValue = 23;
sheet.Range["D6"].NumberValue = 33;
sheet.Range["D7"].NumberValue = 38;

//Create a columnclustered chart
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["B1:D7"];
chart.SeriesDataFromRange = false;
chart.TopRow = 4;
chart.BottomRow = 27;
chart.LeftColumn = 2;
chart.RightColumn =11;
chart.ChartTitle = "Chart with trendline and datatable";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A7"];

//Add trendline and datatable to the chart
chart.Series[0].TrendLines.Add(TrendLineType.Exponential);
chart.HasDataTable = true;

//Save the document as .xlsx file
workbook.SaveToFile("Sample1.xlsx", ExcelVersion.Version2010);
Step 2: Using the same sample data, I create another chart to show the error bars in Excel using C#. Only the different codes are listed.
Chart chart = sheet.Charts.Add(ExcelChartType.Line);
chart.DataRange = sheet.Range["C1:D7"];
chart.SeriesDataFromRange = false;
chart.TopRow = 4;
chart.BottomRow = 27;
chart.LeftColumn = 2;
chart.RightColumn =11;
chart.ChartTitle = "Chart with error bars";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.Series[0].CategoryLabels = sheet.Range["A2:A7"];
chart.Series[0].ErrorBar(true, ErrorBarIncludeType.Plus, ErrorBarType.Fixed, 2);
chart.Series[1].ErrorBar(true, ErrorBarIncludeType.Both, ErrorBarType.Percentage,5);

@Jon Peltier , thank you!

 

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.

@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.

@blaughlin1992 I deleted that series causing the error and added it back.  Error gone.  A mystery.

@blaughlin1992 

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.

@seiscons 

Hello there,

a week ago I downloaded a new language tool for Portuguese, when I did it, Microsoft made minor update to my 365 office, I saw that Outlook installed a better search tool....etc... etc, after this, I started having the issue with Excel, that label indicating trendingline error is in one of my workbooks in all its sheets, I have tested everything but the label remains there, there isn't a clear way to get rid of that label.  

I found a way to turn this off. Under Chart Design, Add Chart Element, choose Trendline > None. And error goes away.