Label points on Chart 3

[mymou]

Hi all


This must be easy

Product return volatility
D 2% 1%
A 10% 3%
B 11% 10%
C 30% 15%

With this data I want to plot return against volatility (like a scatter diagram) and have the points labelled with the product name.

Couldn’t be easier - but how??

If possible to draw 'through' these points like statisticians do - then great.

Any bright ideas

 

[logius]

Are the product names in a column or row? Also, what do you mean by drawing "through" the points? If you mean if it's possible to draw a line that shows the direction the points are taking, then, yes, it's possible. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[mymou]

Hi logius

Product, return, volatility are column headings

D, 2%, 1% is sample data.

I have to plot return against volatility (with the product name at each point).

I do want to show 'the direction the points are taking' (ie not actually join the points) - How do you do that?


Stew

 

[Andie]

Are you trying to create a trendline through the points? If so, go to "Chart | Add Trendline" then select the data field that you want a trend line for - I'm guessing volatility. This shows you the general trend that the data is exhibiting. You can test out different trendlines such as linear, logarithmic, polynomial, etc.

 

[mymou]

Hi Andie

Found trendlines half an hour ago. You get a star anyway.

Have you got a solution for my missing 'chart data series'?
I'm doing something stupid again.


Stew

 

[Andie]

Are you referring to labeling the points with the product name? If so, click on one of the points, right click, "format data series" then select the "data labels" tab. From there, click on Show Label.

If there is another problem with graphing, please explain again. I tried looking for previous posts but wasn't able to find one that was about missing data. Thanks!

 

[mymou]

Hi All


I'll start again

Product return volatility
D 2% 1%
A 10% 3%
B 11% 10%
C 30% 15%

D, 2%, 1% is sample data. This represents one point of data - exactly how I want to graph it. Coordinates (2,1) with a label next to it of 'D'.

The scatter diagram plots the coordinates just fine - but I cant get the product label to appear next to the coordinate. If I try FORMAT DATA SERIES |DATA LABELS | Show Label - it just gives me a value from the x axis.

HELP!!


Stew

 

[Hasit]

Stew,

The way to get the Product labels displayed against each of the points for Return and Volatility is to do the following:

1. Select ALL the columns (in your case assuming Product starts in Column A1), so A1:C5

2. Click on the Graphs button and select the XY Scatter graph.

3. Follow the prompts as you wish, so that you end up with the graph you want.

4. Right click on one of the series and you should be offered the chance to "Add Trendline". Do this for both the Return and Volatility data series.

5. Right click on one of the data series and choose "Format Data Series".

6. Under "Data Labels" tab, choose the "Show Label" radio button and NOT the "Show Value" radio button. At the same time, as choosing the "Show Label" radio button, you will be offered the "Show Legend Key next to Label" check box. Check that box too.

7. Click on OK, and you should have the graph for Volatility, Return and the Product name against each data point in the series.

 

[mymou]

HI Hasit

Thanks for a very complete answer - but unfortunately - it doesn't quite answer my question.

Your solution plots two data series (Product against Volatility, Product against Return). I only have one data series as each pair represents (x,y) coordinates (Volatility against Return).

Hope that is clearer.

Stew

 

[mymou]

Hi Guys

Got the answer from Microsoft.

You need a 'Macro to Add Labels to Points in an XY (Scatter) Chart'. Find article on Microsoft's site.

This is definitely true for 97 - anyone know if it is also true for 2000???


Stew

 

[Hasit]

Aha! I understand now. I am not sure of a way to get what you want. I did however plot the four points in your table and each point was shown as a different element (cross, triangle, square and diamond, representing D, A, B and C). These were defined as the legends and appeared next to the graph rather than next to the individual points themselves.

I am not if this helps, but I have tried several things, and could not get the legends to appear next to their respective points.

 

[mymou]

Hi Hasit

How did you get the different elements showing - I couldn't even get that far.

Stew

 

[Hasit]

When you create the graph, don't choose the data points before clicking on the graphs icon. Instead, click on the icon first, select the XY Scatter graph, and then under the "Series" tab, add each product as a series name. For each series (product) name (D, A, B, C etc), add the two return and volatility data points relevant to the product.

You have to do this 4 times to get the 4 points on your graph.

Send me an email at hbakhda@hotmail.com and I'll send you the spreadsheet and graph I have created.

 

[Hasit]

Just sent the graph to "mymou". He sent me some code to attach labels to data points, which may be worth adding to this thread as others may find it useful:

Sub AttachLabelsToPoints()

'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals 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, ",&quot, xVals, _
Mid(Left(xVals, InStr(xVals, "!&quot - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!&quot, xVals, ",&quot - 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
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter

End Sub