Series() function?

Question

I'm following the code at http://support.microsoft.com/en-us/kb/213750 to learn how to manipulate chart-related objects.  The webpage above deals with adding a series of labels to datapoints on a chart, but my question isn't about that.  It is about understanding the object model as it pertains to chart-related objects as I surf the web to become familiar with them.

Specifically, if I Debug.Print ActiveChart.SeriesCollection(1).Formula, I get =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$12,Sheet1!$B$2:$B$12,1) for a chart that I created.  I tried to look up the Excel 2007 help on what this SERIES function does, but could only find documention on the Series *object*.  I thought the Series object might have a constructor function (like in C++) of the same name as the Series class, but no -- Series has no properties or methods of the same name.

It seems odd that there is no documentation for the arguments of what might be a constructor function.  I can't seem to find anything about a series-method, series-function, or anything along the lines of a constructor.  Can anyone point to such documentation (assuming that it exists!)?

P.S.  I thought that if I typed the following into the Immediate window, Intellisense might show at least what the arguments were, but no joy: Debug.Print SERIES(

And of course, entering =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$12,Sheet1!$B$2:$B$12,1) as a cell formula isn't valid

Answer 1

SERIES is not a worksheet function; it is used on charts only.

The syntax is

=SERIES([series_name], [x_values], y_values, series_order)

series_name specifies the name of a chart series; if this optional argument is omitted, Excel will use a default name such as Series1.

x-values specifies the values on the category axis; if this optional argument is omitted, Excel uses 1, 2, 3, ...

y-values specifies the values on the value axis.

series_order specifies the order of the series; 1 is the first series, 2 the second etc.

You can edit the formula for existing series, and you can add a new series by clicking in an empty part of the chart and entering a SERIES formula with the correct arguments.

Answer 2

See:

http://exceluser.com/excel_help/functions/function-series.htm

http://peltiertech.com/Excel/ChartsHowTo/ChartSeriesFormula.html

Answer 3

Interesting.  So Microsoft doesn't provide official vendor documentation?  (It's rhetorical question).  Thanks.

Answer 4

Thanks, Hans.  Would you know where one would find documentation for such functions?

P.S.  Below, I added my slight modification of the code from the referenced Microsoft site.  For those experienced with manipulating chart objects and dialog boxes, it's trivial, but for those like me who are looking for pre-canned code, it could save them a morning.  The test case is a table:

   Labels  x  y

   dog     2  5

   cat     6  3

   horse   6  5

   mouse   1  1

   ****  3  2

   wok     8  7

   duk     6  8

   cluk    1  3

   suk     3  0

   wuk     2  8

   muk     3  9

Sub AttachLabelsToPoints()

   'Dimension variables.

   Dim Counter As Integer, ChartName As String, xVals As String

   Dim rgLabelHed As Range

   ' ' Disable screen updating while the subroutine is run.

   ' Application.ScreenUpdating = False

   ' 'Store the formula for the first series in "xVals".

   ' xVals = ActiveChart.SeriesCollection(1).Formula

   '''''''''''''''''''''''''''''''''''''''''''''

   ' User selects series

   If TypeName(Selection)<>"Series" Then

      MsgBox("Please select a chart series first.")

      Exit Sub

   End If

   xvals = Selection.Formula

   'Extract the range for the data from xVals.

   xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _

      Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))

   xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)

   Do While Left(xVals, 1) = ","

      xVals = Mid(xVals, 2)

   Loop

   'Get top of column range for labels

   '----------------------------------

   On Error Resume Next

   Set rgLabelHed = Application.InputBox( _

      "Select column heading for the labels", _

      "Labels", Type:=8)

   On Error GoTo 0

   If rgLabelHed Is Nothing Then Exit Sub

   'Attach a label to each data point in the chart.

   For Counter = 1 To Range(xVals).Cells.Count

     ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _

         True

      ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _

         rgLabelHed(Counter+1,1)

   Next Counter

End Sub