How to make a trendline invisible?

[Guest]

Hello,

I have several charts and several trendlines on them. How can I hide the
trendlines (make them invisible)from VB code?

Thanks in advance for the help,
Zoltan

 

[Guest]

I don't have code for you but if you make the trendline the same color as the
chart background and use the smallest weight it will seem "invisible." Just
an idea.

 

[Guest]

Not sure about the visible property for trendlines but this will make them go
away.

Charts("Chart1").SeriesCollection(1).Trendlines.Delete

 

[Guest]

What if I do not want them to go away? I am using them for calculations (the
equation of them), so I need them. I just do not want to confuse the user by
the lots of trendlines on the chart. Showing them the data points is enough.

Matching the color of the trendline to the color of the background is one
solution, but at the gridlines it will make some confusion.

I hope there is another way.

Thanks in advance,
Zoltan

 

[Guest]

I couldn't find any info on doing this. Using xlColorIndex none gives an
error as does trying to set the border weight to 0 (doesn't exist, I think).
What about getting the regression equation from the data analysis functions
and skipping trendlines all together?

 

[Guest]

OK. I recorded a macro that adds a trendline for a series (log in my case),
copies the equation and pastes it to the edge of the chart, then clears the
trendline. Maybe that will do it? This is just an example and will need to
be cleaned up for your purpose:

Sub No_trendline()
'
' No_trendline Macro
' Macro recorded 8/31/2007 by a2holder
'

'
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLogarithmic,
Forward _
:=0, Backward:=0, DisplayEquation:=True,
DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
ActiveChart.ChartArea.Select
ActiveChart.Paste
ActiveChart.SeriesCollection(1).Trendlines(1).Select
Selection.Delete
End Sub

 

[Jon Peltier]

You could use LINEST to calculate the coefficients in the worksheet from the
data. Bernard has a simple explanation:

http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

while Tushar has gone into a great amount of detail:

http://tushar-mehta.com/publish_train/data_analysis/16.htm

- Jon

 

[Guest]

Thanks Arnie,
this is what I will do. It seems, that it is the best solution.

Jon,
you suggested to use LINEST, but I found some discussions about this
funtions, and I read that LINEST is not too accurate. That is why I need to
use the trendline. Anyway, I will give a try, and see the difference between
trendline and linest results.

Thanks both of you!
Zoltan

 

[Jon Peltier]

For most data sets, LINEST and the trendline equation produce the same
result. In Excel 2003, there was a change made to the formerly best-in-class
trendline formula calculation which may lead to false results (changing a
small value to zero in an erroneous assumption that the small number is due
to roundoff when converting from binary to decimal), where the LINEST
calculations stay accurate. Also, people often forget to read the trendline
coefficients with sufficient digits to use in subsequent calculations.

- Jon