John:

I like your idea about adding data array for the average line. You ran into
a problem because you were adding values for every point in original data
series.

To plot average line, we only need 2 points (begin and end).

I modified your code to add an average horizontal line without any new data
added to sheets. Basically, it just adds the X & Y min and X and Y max
values, not all the points along original data series.

The code includes a data label for avg line for editing purposes.

Public Sub Plot_avg()
' Procedure to plot avg line in activechart
Dim x_1 As Double ' Min x value
Dim x_2 As Double ' Max x value
' Remove previous Series(2) avg line
On Error Resume Next ' needed in
case no previous series(2) Avg Line
ActiveChart.SeriesCollection(2).Select
Selection.Delete
' Determine num data points
p = ActiveChart.SeriesCollection(1).Points.Count
' Calc Averge
calc_mean = Application.Average(Range("B:B"))
' Determine start and end X values
x_1 = Application.WorksheetFunction.Min(Range("A2:A1000" ))
x_2 = Application.WorksheetFunction.Max(Range("A2:A1000" ))
' SetSeriescollection Data array {X_1,X_2} & {calc_avg,calc_avg} - Notice
{}'s for array
x_values = "{" & x_1 & "," & x_2 & "}"
y_values = "{" & calc_mean & "," & calc_mean & "}"
' Add new series
With ActiveChart.SeriesCollection.NewSeries
.XValues = x_values
.Values = y_values
' Add data label to last pt on avg line
.Points(2).ApplyDataLabels
End With
End Sub

...Kelly





"John Michl" wrote in message
oups.com...
Turns out the problems I was having had nothing to do with decimals but
rather the size of the array I was creating. It appears that I can't
enter a string into the ActiveChart.SeriesCollection(2).Values when the
length of that string is greater than 255 characters. I had no problem
when I had a dozen or so data points but when I had 52 (one for each
week in a year) I started to bump into problems. Through trial and
error I determined it was the length of the string being created in my
code that was causing the problem.

I really wanted to avoid using a dummy column of data for this but it
looks like that's what I'll need to do.

- John