Trouble with graphs!
Alison
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
Andy Pope
I think you would be better off with the line chart.
Pupil along the X axis, evenly spaced, score on the Y axis.
Draw a normal line with markers chart.
Then format the Line to have No Line Pattern.
Grade
Andy 7
Bob 6
Charile 7
Dave 4
--
Cheers
Andy
Alison
in as much of a vertical line as possible as I want to
have several classes in a line. I still have the same
problem if you try and squash up the point.
Thanks again
Alison
>.
>
Debra Dalgleish
http://www.geocities.com/jonpeltier/Excel/Charts/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
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
Tushar Mehta
What are you plotting on the x- and the y-axis?
In a related note you wrote "...as I want to have several classes in a
line..." What does that mean in terms of visually displaying
information in an XL chart?
--
Regards,
Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article <0ab901c34c5c$7d5a54c0$a101...@phx.gbl>,
alison....@feltham.hounslow.sch.uk says...
Alison
>
Jon Peltier
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
_______
Tushar Mehta
some months back while flying from somewhere to somewhere. Obviously,
it's languishing on one of the laptops. I wonder if I even finished
it.
A couple of differences. I didn't use the dummy A,B 0,0 series, which
is a nice touch. Instead, I used just a XY Scatter chart with larger
spacing between the categories (class in your case). In your example,
I might use Class*15+Delta. I also plotted the different categories as
different series so that each had a different marker (color/shape).
And, I used a MOD(,2)=1 test to assign a sign to what is Delta in your
case -- with a INT(x/COUNTIF()) to calculate increasing values of
Delta.
--
Regards,
Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article <3F1997AC...@yahoo.com>, jonpe...@yahoo.com says...
Jon Peltier
To answer this one, I dug out an old example for a client. She was
comparing historical data, in the form of a box and whisker chart, and
wanted the markers for the current evaluation period next to the
corresponding box/whisker. I'd used a different algorithm, in which the
density of points increased with the number at each level. That didn't
work here, because of labels overlapping. So I picked a uniform
horizontal spacing for the points. But once you have the basic concept
down, it's pretty easy to adapt it to the particular needs of the moment.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
