• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding Trendline in Excel [SOLVED]

A

ang.ru

New Member
Hi all,


I'm looking at roughly 200 graphs on a single spreadsheet, is there a option in Excel where I can add a trendline to all 200 at once, instead of doing it graph by graph?
 
Ang.Ru


This little bit of code will do that


Copy /Paste it into a Code Module in VBA

Select the chart

Run the code

[pre]
Code: 
Sub Add_Trendlines()
With ActiveChart
For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Trendlines.Add Type:=xlLinear, Name:="Linear Trend - " + .SeriesCollection(i).Name
Next i
End With
End Sub
[/pre]
 
Thanks Hui, you've saved me so much time!!! Also, is there a function in excel that can automatically pick up trends (upward, downward, horizontal) and categorize them for me?
 
Linear trends will use the format of Y = mX +c

where m is the gradient of the line


So you can derive that using the Linest or Gradient functions on the source data

You can then use another column to assign then as Positive, Negative or neutral according to some criteria


have a read of: http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/

about how Linest works
 
Hi, ang.ru!


Taking Hui's solution and regarding the amount of charts you could try this:

-----

[pre]
Code: 
Option Explicit

Sub Add_All_Trendlines()
Dim I As Integer, chtobj As ChartObject
For Each chtobj In ChartObjects
With chtobj.Chart
For I = 1 To .SeriesCollection.Count
.SeriesCollection(I).Trendlines.Add Type:=xlLinear, Name:="Linear Trend - " + .SeriesCollection(I).Name
Next I
End With
Next chtobj
End Sub
[/pre]
-----


Place it in the VBA object section of the worksheet containing the charts and run it only once.


Regards!
 
Hi, ang.ru!

Glad to help, but all credit to Hui, I only embraced his code within a simple loop. Tell us if it works as you expected.

Regards!
 
Thanx SirJB


I misread that and thought he had 200 series in a chart,

Which I did think that was a bit odd and should have double checked
 
@Hui

Hi!

I thought you were in a hurry (don't ask me why, just sixth sense if I was right) so I added my two cents.

Regards!
 
SirJB7,


For one of my spreadsheets, the code worked perfectly, but for another spreadsheet, I got a popup from excel saying "Compile Error: Variable Not Defined". Does anyone know why/what I can do to fix it?
 
Hi, ang.ru!


A few questions:

a) Did you copy the whole code into the worksheet code VBA section?

b) Does this workbook has the same type of charts than the other one?

c) If you entered in debug mode (automatically when displayed the error) would you type what follows in the Immediate window (Ctrl-G if you don't see it) and paste the printed values?

?chtobj.Name, I


However it'd be useful if you could upload the file.


Regards!
 
The above code will only work on Excel 2007+ files

It won't work on Excel 97-03 files even in compatibility mode
 
Hi SirJB7!


a. Yes

b. Yes

c. I tried and it didn't fix the situation.


Any other ideas?


Though it would be a lot easier for me to upload the file, I unfortunately can't because everything on it is confidential.
 
Hi, ang.ru!


Never used a Mac for any serious thing :P

Even Excel isn't too serious neither :=)


Tried anonymizing the data as stated here?

http://chandoo.org/forums/topic/posting-a-sample-workbook


BTW, when you issued "?chtobj.Name, I" in the Immediate window that shouldn't solve anything, it was only for identifying where exactly the problem was so as to check it against the file. Could you copy and paste what was displayed?


Regards!
 
Sure thing!


here's a link for just a little sample of what i have : https://www.dropbox.com/s/0idba1416bwk8un/Workbook12.xlsx


and when I issued "?chtobj.Name, I", it identified "Sub Add_All_Trendlines()" as the error. What exactly does variable not defined refer to?


thank you!
 
Hi, ang.ru!


It worked fine with your sample file:

https://dl.dropboxusercontent.com/u/60558749/Adding%20Trendline%20in%20Excel%20-%20Workbook12%20%28for%20ang.ru%29.xlsm


I added the code to Chart4 and saved the workbook as .xlsm since it was an .xlsx.


When at Immediate window pane, if you issued "?chtobj.Name, I" (unquoted), or maybe "Print chtobj.Name, I" just in case at Mac differs) you'd get to variables, the name of the chart and the no. of the series.


Regards!
 
You must log in or register to reply here.
Back
Top