> Too bad you solved it, because I have another approach. It's actually

> related, but allows for multiple classes to be lined up above a

> different X axis position. A single occurrence of a score is plotted

> above the axis label. If there are two occurrences, they are spread a

> bit left and right. If there are three, one is centered and the other

> two are spread a bit further.

>

> Sample data, starting in A2, explanation below:

>

> Class Cumul. Total Delta X Name Score 0.07

> 1 1 4 -3 0.79 AA 22

> 1 1 2 -1 0.93 BB 24

> 1 2 2 1 1.07 CC 24

> 1 1 3 -2 0.86 DD 18

> 1 2 4 -1 0.93 EE 22

> 1 2 3 0 1.00 FF 18

> 1 1 1 0 1.00 GG 15

> 1 3 3 2 1.14 HH 18

> 1 1 1 0 1.00 II 19

> 1 3 4 1 1.07 JJ 22

> 1 4 4 3 1.21 KK 22

> 1 1 1 0 1.00 LL 16

> 2 1 4 -3 1.79 MM 17

> 2 1 2 -1 1.93 NN 18

> 2 1 2 -1 1.93 OO 19

> 2 2 4 -1 1.93 PP 17

> 2 3 4 1 2.07 QQ 17

> 2 2 2 1 2.07 RR 18

> 2 1 2 -1 1.93 SS 25

> 2 2 2 1 2.07 TT 25

> 2 1 1 0 2.00 UU 24

> 2 2 2 1 2.07 VV 19

> 2 4 4 3 2.21 WW 17

> 2 1 1 0 2.00 XX 20

>

> This has scores for two classes (1 & 2). The Cumul. column (b) has the

> number so far with the same score in the same class. B3 has this array

> formula

>

> {=SUM(($G3:$G$3=G3)*($A3:$A$3=A3))}

>

> Don't type in the curly brackets; type the formula, then hold Ctrl-Shift

> while pressing Enter, and Excel adds the brackets. Drag this down to

> B26. The Total column (C) has the total number in the same class with

> the same score. C3 has this array formula

>

> {=SUM(($G$3:$G$26=G3)*($A$3:$A$26=A3))}

>

> dragged down to C26. Delta is just a column with an intermediate

> computation which will lead to the offset of the points from the center.

> The formula in D3 is

>

> =2*B3-C3-1

>

> and it's dragged down to D26. Finally, the X column has the actual X

> value for the student's plotted point. The formula in E3 is:

>

> =A3+D3*H$2

>

> H2 holds the standard horizontal offset (0.07 in my case, but it's

> adjustable) for points with the same score. In the histogram example

> Debra cited, the offset was 1, but was derived differently.

>

> All the formulas in columns B:E could be combined into a single column,

> but it's always easier to set these things up piecewise.

>

> Finally, Name and Score are your raw data. I used initials in place of

> names, because they are shorter, and less prone to being obscured by

> each other.

>

> Almost ready to chart the data. I set up a dummy range off to the side:

>

> Class

> A 0

> B 0

>

> A and B are the designations for the two classes being plotted in this

> example. Select this range and construct a column chart. You get A and

> B for category axis labels, and no columns appear because the values are

> 0. You could put the class averages into the cells instead of zeros,

> and these will show up on the chart. It would show that Class A (1 in

> the main table) did slightly better than class B.

>

> Now select the column with the X values, and hold the Ctrl key and

> select the corresponding scores. Copy this discontiguous range, click

> on the chart, and select Paste Special from the Edit menu. Add this as

> a new series, with categories in the first column.

>

> You get another column series added to the chart. Right click on the

> new series, choose Chart Type from the pop up menu, and pick out the

> Scatter chart type with markers and no lines. Okay your way back to the

> chart. Right click on the chart, and choose Chart Options from the pop

> up menu. Click on the Axes tab, and uncheck both secondary axes.

>

> Now use Rob Bovey's Chart Labeler (http://appspro.com) to put the

> initials onto the chart. What I did was center the labels right on the

> charted points, then formatted the points to have no markers; the labels

> are now the markers.

>

> - Jon

> -------

> Jon Peltier, Microsoft Excel MVP

> http://www.geocities.com/jonpeltier/Excel/index.html

> _______

>

> Alison wrote:

> > This is great! Thanks ever so much. Problem solved!

> >

> >>-----Original Message-----

> >>Another option is to use a custom histogram:

> >>

> >>

> >

> > http://www.geocities.com/jonpeltier/...arts/Histogram.

> > html

> >

> >>You could skip steps 8 and 9, and leave the markers as

> >

> > dots.

> >

> >>Then, use Rob Bovey's free add-in, the XY Chart Labeler,

> >

> > to add the labels:

> >

> >> http://www.appspro.com/utilities/Labeler.asp

> >>

> >>

> >>Alison wrote:

> >>

> >>>Hi,

> >>>

> >>>I hope someone can help. I'm trying to do a graph for

> >>>students test results in my class. The idea is that

> >>

> > each

> >

> >>>child will have a spot and they can find their spot on

> >>

> > the

> >

> >>>graph. The trouble is that if you choose a scatter

> >>

> > graph

> >

> >>>any students that have the same score appear as one

> >>

> > spot

> >

> >>>and I want them each to have their own spot eg 3 in a

> >>

> > row

> >

> >>>with a score of 65 etc on the y-axis. I've tried

> >>

> > scaling

> >

> >>>down the x-axis but it looks untidy and you lose

> >>

> > points.

> >

> >>>There must be an easy way to do it, this is driving me

> >>>crazy! Any help will be greatly appreciated.

> >>>

> >>>Thanks

> >>>Ali

> >>

> >>

> >>--

> >>Debra Dalgleish

> >>Excel FAQ, Tips & Book List

> >>http://www.contextures.com/tiptech.html

> >>

> >>.

> >>

> >

Hello, is it necessary to replace the x1, y1, etc values when adding them to the VBA?

]]>