Help needed - Making charts

[shallwani]

I have 50 years data for money growth and inflation and I want to make a scatter chart to present this as follows;

X axis: Money growth
Y axis: Inflation

And year to be shown in the middle with labels on it.

Can somebody help please?

 

[sijpie]

OK,

With your data organised like this in a table

Year	Growth	Inflation
1985	0.08	0.02
1986	0.07	0.03
1987	0.08	0.04
1988	0.09	0.02
1989	0.07	0.1
1990	0.06	0.04
1991	0.08	0.02
1992	0.09	0.03
1993	0.09	0.05
1994	0.08	0.02
1995	0.07	0.08
1996	0.08	0.04

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Make your scatter graph by highlighting the Growth and Inflation columns and creating a scatter graph.
Delete the legend.

Now in the macro editor (Alt-F11) create a module (by right clicking on the filename in the left pane of the editor and choosing Insert... Module) and paste the following macro in there:

Option Explicit

Sub grphLabel()
   'Dimension variables.
   Dim Counter As Integer, ChartName As String, xVals As String
   Dim sLbl As String, sStg As String


   ' 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


   '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
   'Attach a label to each data point in the chart.
   For Counter = 1 To Range(xVals).Cells.Count
     With ActiveChart.SeriesCollection(1).Points(Counter)
         sLbl = Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
         '## the -1 in the Offset above is the postion of the column _
          ## holding the year compared to the rightmost column of _
          ## graph values
        .HasDataLabel = True
        .DataLabel.Text = sLbl
        If Range(xVals).Cells(Counter, 1).Value < Range(xVals).Cells(Counter, 1).Offset(0, 1).Value Then
            .MarkerBackgroundColor = RGB(255, 50, 50)
            .MarkerForegroundColor = RGB(255, 50, 50)
            .MarkerStyle = xlMarkerStyleDiamond
            .MarkerSize = 7
            With .DataLabel.Format
                .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 50, 50)
            End With
        Else
            .MarkerBackgroundColor = RGB(0, 0, 255)
            .MarkerForegroundColor = RGB(0, 0, 255)
            .MarkerStyle = xlMarkerStyleDiamond
            .MarkerSize = 7
            With .DataLabel.Format
                .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(20, 20, 255)
            End With
        End If
    End With
   Next Counter


End Sub

Now make sure you have the graph selected, and run the macro (either by pressing the green 'play' arrow in the macro editor toolbar, or by going Developer tab, run Macro )

This should not only set the years as labels next to the markers, but will also colour them red if the growth was less than the inflation. (if you don't like that I'll tell you what to delete from the macro)

 

[shallwani]

This is something I was looking for!!


Many thanks for your support.