Scatter diagram of more than one series

[outtahere]

Hi I would like to present a scatter diagram showing performance on one axis and volatility on the other. I then need to do this for several items. I have used the chart to try this but I get Series 1 and a great scatter diagram but I do not know how to get the left hand labels relating to the two figures on the right of it.

Perf vol
FSAP 1.5 3.4
BED 1.3 4
SUKA 3.3 4
AEM 3.9 3.7
MGR 6 3.6

 

[tusharm]

Surprisingly, there isn't a simple way to do what you want.

Here are three options:

1) Select the numbers in the 2 columns. Create a XY Scatter chart.

Then, use a 3rd party product -- Rob Bovey's freeware XY Chartlabeler from www.appspro.com or my own shareware product TM Chart Utilities (http://www.tushar-mehta.com/excel/software/chart_utilities/index.html) to add the items in column 1 as the labels for the data points.

Optionally, change the settings for the chart series to show each data point in a different format.

2) Create a chart from the 2 numbers in row 2 (row 1 has headers). Use the chart UI to add the 1st column in row 2 as the name of the series. Repeat the process for every row.

3) Use the code below to implement 2 above.

Option Explicit

Sub addScatterChartByRow()
    Dim WS As Worksheet: Set WS = ActiveSheet
    Dim Rng As Range: Set Rng = Selection.CurrentRegion
    Dim Cht As Chart
    Set Cht = Charts.Add
    With Cht
    Do While .SeriesCollection.Count > 0: .SeriesCollection(1).Delete: Loop
    .ChartType = xlXYScatter
    Set Cht = .Location(Where:=xlLocationAsObject, Name:=WS.Name)
        End With
    Dim I As Integer
    For I = 2 To Rng.Rows.Count
        With Cht
        Dim aSeries As Series
        .SeriesCollection.Add Source:=Rng.Rows(I).Offset(0, 1).Resize(, 2), _
            Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=True, Replace:= _
            False
        Set aSeries = .SeriesCollection(.SeriesCollection.Count)
        With aSeries
        .Name = Rng.Rows(I).Cells(1).Value
            End With
            End With
        Next I
    End Sub

Hi I would like to present a scatter diagram showing performance on one axis and volatility on the other. I then need to do this for several items. I have used the chart to try this but I get Series 1 and a great scatter diagram but I do not know how to get the left hand labels relating to the two figures on the right of it.

Perf vol
FSAP 1.5 3.4
BED 1.3 4
SUKA 3.3 4
AEM 3.9 3.7
MGR 6 3.6

 

[outtahere]

Surprisingly, there isn't a simple way to do what you want.

Here are three options:

1) Select the numbers in the 2 columns. Create a XY Scatter chart.

Then, use a 3rd party product -- Rob Bovey's freeware XY Chartlabeler from www.appspro.com or my own shareware product TM Chart Utilities (http://www.tushar-mehta.com/excel/software/chart_utilities/index.html) to add the items in column 1 as the labels for the data points.

Optionally, change the settings for the chart series to show each data point in a different format.

2) Create a chart from the 2 numbers in row 2 (row 1 has headers). Use the chart UI to add the 1st column in row 2 as the name of the series. Repeat the process for every row.

3) Use the code below to implement 2 above.

Option Explicit

Sub addScatterChartByRow()
    Dim WS As Worksheet: Set WS = ActiveSheet
    Dim Rng As Range: Set Rng = Selection.CurrentRegion
    Dim Cht As Chart
    Set Cht = Charts.Add
    With Cht
    Do While .SeriesCollection.Count > 0: .SeriesCollection(1).Delete: Loop
    .ChartType = xlXYScatter
    Set Cht = .Location(Where:=xlLocationAsObject, Name:=WS.Name)
        End With
    Dim I As Integer
    For I = 2 To Rng.Rows.Count
        With Cht
        Dim aSeries As Series
        .SeriesCollection.Add Source:=Rng.Rows(I).Offset(0, 1).Resize(, 2), _
            Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=True, Replace:= _
            False
        Set aSeries = .SeriesCollection(.SeriesCollection.Count)
        With aSeries
        .Name = Rng.Rows(I).Cells(1).Value
            End With
            End With
        Next I
    End Sub

Hi, Tushar, thanks. I would like to wait to see if there is a simpler solution. I realise I did not display the date properly as it does not show the headers above each column of figures. I would prefer not to start writing code as this is not my line of business. Is the shareware idiot proof or do I need to spend hours installing and reading instructions?