Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.charting




Trouble with graphs!
On Saturday, July 19, 2003 at 10:10:36 PM UTC+3, Jon Peltier wrote:
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 CtrlShift 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*B3C31 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 addin, 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 yaxis. I've tried scaling down the xaxis 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? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Trouble with Excel Stacking Bar Graphs  Excel Discussion (Misc queries)  
Trouble copying graphs  Excel Discussion (Misc queries)  
Charts/ Graphs and Dates Trouble  Charts and Charting in Excel  
Charts/ Graphs and Dates Trouble  Excel Discussion (Misc queries)  
having trouble with graphs  Excel Discussion (Misc queries) 