Error When Trying to Calculate R Squared With Only One Datapoint

[Mike C]

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

 

[Allen_N]

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]

That was the purpose of the line that reads:

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

 

[Mike C]

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

 

[Mike C]

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