Scatter graph problem

[Sue]

Hi,

I have created a scatter graph showing the results of 200+ pupils in two
tests. The x axis shows the result of test A, the v axis test B.

My problem is that some of the points on the graph represent 1 pupil and
another may represent 70. Please can someone tell me how I can indicate the
number of pupils for each point.

Thanks,

Sue

 

[Tushar Mehta]

I'm curious how the data are organized and how the graph is created.
So, if you don't mind sharing that...

In the meantime, specific to your question, if you have the count
(i.e., the frequency) data in some column, you could use Rob Bovey's XY
Chartlabeler (available from www.appspro.com). Use it to label the
series with the frequency column. Center the labels, and set the data
marker to 'None'. To do the last bit, double click the plotted series
on the chart, then select the Patterns tab.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Sue -

A pivot table will help to make this chart. I'll work through this
example with the following data.

X Y
13 10
15 10
11 10
10 14
14 14
12 11
10 10
11 14
13 12
13 13
14 13
11 11
14 15
15 10
14 11
13 10
12 14
11 11
11 11
10 11
11 14

Select the range with your data, then choose Pivot Table Report from
the Data menu. When you get to the Layout step, put Y in the Rows area,
and X below it in the Rows area. Drag Y into the Data area, and make it
display the Average of Y; again drag Y to the Data area, make this one
display Count of Y. The top part of the pivot table looks like this:

Y X Data Total
10 10 Avg Y 10
Count Y 1
11 Avg Y 10
Count Y 1
13 Avg Y 10
Count Y 2
15 Avg Y 10
Count Y 2
10 Avg Y 10
10 Count Y 6
11 10 Avg Y 11
Count Y 1

Double click the buttons for the Y and X fields, and set the Subtotals
to None. Then drag the button for the Data field above the Total label.
Now the pivot table looks like this:

Data
Y X Avg Y Count Y
10 10 10 1
11 10 1
13 10 2
15 10 2
11 10 11 1
11 11 3
12 11 1
14 11 1
12 13 12 1
13 13 13 1
14 13 1
14 10 14 1
11 14 2
12 14 1
14 14 1
15 14 15 1

You need to generate an XY Scatter chart from the X and Avg Y columns.
If you're using Excel 2000 or later, Excel will try to make a Pivot
Chart. To avoid this aberrant behavior, select a blank chart and start
the chart wizard. On step 1, select a scatter chart. On step 2, click
on the Series tab, click add, type whatever name you want (or click in
the Name box and then select a cell that contains the name), clear the Y
Values box and select the Avg Y data in the pivot table, then click in
the X Values box and select the X values in the pivot table.

Finally use Rob Bovey's Chart Labeler (a free Excel addin from
http://appspro.com) to apply the labels in the Count Y column to the
data points.

- Jon