I have code (exerpt below) that loops through a recordset, adds a trendline
to a graph, and puts the R Squared value in a table. The problem is that some
of the graphs only have one datapoint i.e. no trendline, so it throws an
error that says "unable to set the displayrsquared property of the trendline
class". I tried to count the datapoints in the series collection first and
only add a trendline if the number of datapoints is greater than 1 but it
doesn't seem to work. Any thoughts? Thanks!!!

MarketShareProductIMS3Dollar.SeriesCollection

If MarketShareProductIMS3Dollar.SeriesCollection(1).Points.Count > 1 Then

Set mySheet = MarketShareProductIMS3Dollar.Application.DataSheet

Company = mySheet.Cells(1, i + 1)
With MarketShareProductIMS3Dollar.SeriesCollection(1)

.Trendlines.ADD Type:=xlLinear, Name:=Company & " Linear
Trend"
.Trendlines(1).DisplayRSquared = True
End If

Just a guess, but you might have to restrict to data sets with > 2 points. A
trendline fitted to 2 points will have a zero 'sum of squares of residuals',
which might cause a divide-by-zero somewhere.

"Mike C" wrote:

> I have code (exerpt below) that loops through a recordset, adds a trendline
> to a graph, and puts the R Squared value in a table. The problem is that some
> of the graphs only have one datapoint i.e. no trendline, so it throws an
> error that says "unable to set the displayrsquared property of the trendline
> class". I tried to count the datapoints in the series collection first and
> only add a trendline if the number of datapoints is greater than 1 but it
> doesn't seem to work. Any thoughts? Thanks!!!
>
> MarketShareProductIMS3Dollar.SeriesCollection
>
> If MarketShareProductIMS3Dollar.SeriesCollection(1).Points.Count > 1 Then
>
> Set mySheet = MarketShareProductIMS3Dollar.Application.DataSheet
>
> Company = mySheet.Cells(1, i + 1)
> With MarketShareProductIMS3Dollar.SeriesCollection(1)
>
> .Trendlines.ADD Type:=xlLinear, Name:=Company & " Linear
> Trend"
> .Trendlines(1).DisplayRSquared = True
> End If

That was the purpose of the line that reads:

If MarketShareProductIMS3Dollar.SeriesCollection(1).Points.Count > 1 Then

"Allen_N" wrote:

> Just a guess, but you might have to restrict to data sets with > 2 points. A
> trendline fitted to 2 points will have a zero 'sum of squares of residuals',
> which might cause a divide-by-zero somewhere.
>
> "Mike C" wrote:
>
> > I have code (exerpt below) that loops through a recordset, adds a trendline
> > to a graph, and puts the R Squared value in a table. The problem is that some
> > of the graphs only have one datapoint i.e. no trendline, so it throws an
> > error that says "unable to set the displayrsquared property of the trendline
> > class". I tried to count the datapoints in the series collection first and
> > only add a trendline if the number of datapoints is greater than 1 but it
> > doesn't seem to work. Any thoughts? Thanks!!!
> >
> > MarketShareProductIMS3Dollar.SeriesCollection
> >
> > If MarketShareProductIMS3Dollar.SeriesCollection(1).Points.Count > 1 Then
> >
> > Set mySheet = MarketShareProductIMS3Dollar.Application.DataSheet
> >
> > Company = mySheet.Cells(1, i + 1)
> > With MarketShareProductIMS3Dollar.SeriesCollection(1)
> >
> > .Trendlines.ADD Type:=xlLinear, Name:=Company & " Linear
> > Trend"
> > .Trendlines(1).DisplayRSquared = True
> > End If

Now I see what you're saying, Allen. Thank you for the suggestion but that
did not work either.

"Allen_N" wrote:

> Just a guess, but you might have to restrict to data sets with > 2 points. A
> trendline fitted to 2 points will have a zero 'sum of squares of residuals',
> which might cause a divide-by-zero somewhere.
>
> "Mike C" wrote:
>
> > I have code (exerpt below) that loops through a recordset, adds a trendline
> > to a graph, and puts the R Squared value in a table. The problem is that some
> > of the graphs only have one datapoint i.e. no trendline, so it throws an
> > error that says "unable to set the displayrsquared property of the trendline
> > class". I tried to count the datapoints in the series collection first and
> > only add a trendline if the number of datapoints is greater than 1 but it
> > doesn't seem to work. Any thoughts? Thanks!!!
> >
> > MarketShareProductIMS3Dollar.SeriesCollection
> >
> > If MarketShareProductIMS3Dollar.SeriesCollection(1).Points.Count > 1 Then
> >
> > Set mySheet = MarketShareProductIMS3Dollar.Application.DataSheet
> >
> > Company = mySheet.Cells(1, i + 1)
> > With MarketShareProductIMS3Dollar.SeriesCollection(1)
> >
> > .Trendlines.ADD Type:=xlLinear, Name:=Company & " Linear
> > Trend"
> > .Trendlines(1).DisplayRSquared = True
> > End If

all set. the problem was with null values in my recordset not with the code
itself per say. thanks.

"Mike C" wrote:

> I have code (exerpt below) that loops through a recordset, adds a trendline
> to a graph, and puts the R Squared value in a table. The problem is that some
> of the graphs only have one datapoint i.e. no trendline, so it throws an
> error that says "unable to set the displayrsquared property of the trendline
> class". I tried to count the datapoints in the series collection first and
> only add a trendline if the number of datapoints is greater than 1 but it
> doesn't seem to work. Any thoughts? Thanks!!!
>
> MarketShareProductIMS3Dollar.SeriesCollection
>
> If MarketShareProductIMS3Dollar.SeriesCollection(1).Points.Count > 1 Then
>
> Set mySheet = MarketShareProductIMS3Dollar.Application.DataSheet
>
> Company = mySheet.Cells(1, i + 1)
> With MarketShareProductIMS3Dollar.SeriesCollection(1)
>
> .Trendlines.ADD Type:=xlLinear, Name:=Company & " Linear
> Trend"
> .Trendlines(1).DisplayRSquared = True
> End If