Charting Multiple Clusters on same graph

[erogalski]

Hello,

I don't have much experience with charts in excel and I'm not sure how to build this chart I want with my data set. Below is a sample of the data:

[TABLE="width: 380"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]School ID[/TD]
[TD]Grade Level[/TD]
[TD]Size[/TD]
[TD]$ Per Pupil[/TD]
[TD]Poverty Level[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]High School[/TD]
[TD]Medium[/TD]
[TD] 13,679.74[/TD]
[TD="align: right"]82.31%[/TD]
[/TR]
[TR]
[TD]7100[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 14,730.47[/TD]
[TD="align: right"]87.73%[/TD]
[/TR]
[TR]
[TD]7730[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 13,561.13[/TD]
[TD="align: right"]89.72%[/TD]
[/TR]
[TR]
[TD]4020[/TD]
[TD]High School[/TD]
[TD]Medium[/TD]
[TD] 12,323.29[/TD]
[TD="align: right"]76.69%[/TD]
[/TR]
[TR]
[TD]4140[/TD]
[TD]High School[/TD]
[TD]Small[/TD]
[TD] 13,463.25[/TD]
[TD="align: right"]85.20%[/TD]
[/TR]
[TR]
[TD]1260[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 12,483.32[/TD]
[TD="align: right"]89.64%[/TD]
[/TR]
[TR]
[TD]6350[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 11,607.09[/TD]
[TD="align: right"]78.71%[/TD]
[/TR]
[TR]
[TD]6360[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 12,554.60[/TD]
[TD="align: right"]90.61%[/TD]
[/TR]
[TR]
[TD]7360[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 11,491.74[/TD]
[TD="align: right"]90.26%[/TD]
[/TR]
[TR]
[TD]1300[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 12,475.01[/TD]
[TD="align: right"]85.06%[/TD]
[/TR]
[TR]
[TD]6390[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 11,548.89[/TD]
[TD="align: right"]86.50%[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]High School[/TD]
[TD]Medium[/TD]
[TD] 14,660.81[/TD]
[TD="align: right"]75.12%[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]High School[/TD]
[TD]Medium[/TD]
[TD] 14,590.71[/TD]
[TD="align: right"]82.00%[/TD]
[/TR]
[TR]
[TD]6060[/TD]
[TD]High School[/TD]
[TD]Medium[/TD]
[TD] 13,464.63[/TD]
[TD="align: right"]76.36%[/TD]
[/TR]
[TR]
[TD]4350[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 12,954.86[/TD]
[TD="align: right"]87.39%[/TD]
[/TR]
[TR]
[TD]4320[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 13,003.60[/TD]
[TD="align: right"]73.82%[/TD]
[/TR]
[TR]
[TD]5350[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 12,761.92[/TD]
[TD="align: right"]92.18%[/TD]
[/TR]
[TR]
[TD]5620[/TD]
[TD]High School[/TD]
[TD]Small[/TD]
[TD] 15,053.34[/TD]
[TD="align: right"]80.00%[/TD]
[/TR]
[TR]
[TD]5610[/TD]
[TD]High School[/TD]
[TD]Small[/TD]
[TD] 13,822.31[/TD]
[TD="align: right"]82.49%[/TD]
[/TR]
[TR]
[TD]4300[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 11,357.39[/TD]
[TD="align: right"]86.19%[/TD]
[/TR]
[TR]
[TD]1460[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 13,305.63[/TD]
[TD="align: right"]81.93%[/TD]
[/TR]
[TR]
[TD]7010[/TD]
[TD]High School[/TD]
[TD]Large[/TD]
[TD] 12,056.31[/TD]
[TD="align: right"]79.09%[/TD]
[/TR]
[TR]
[TD]1410[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 11,239.46[/TD]
[TD="align: right"]89.65%[/TD]
[/TR]
[TR]
[TD]1420[/TD]
[TD]Elementary School[/TD]
[TD]Medium[/TD]
[TD] 13,267.79[/TD]
[TD="align: right"]87.29%[/TD]
[/TR]
[TR]
[TD]8010[/TD]
[TD]High School[/TD]
[TD]Large[/TD]
[TD] 12,559.10[/TD]
[TD="align: right"]69.40%[/TD]
[/TR]
[TR]
[TD]7120[/TD]
[TD]High School[/TD]
[TD]Large[/TD]
[TD] 12,801.50[/TD]
[TD="align: right"]73.37%[/TD]
[/TR]
</tbody>[/TABLE]

What I'm trying to do is make a cluster chart with the Poverty level on the X axis and the $ per pupil on the Y axis. Then plot each school on the graph, but have 6 distinct clusters (Elementary-small, Elementary-medium, Elementary-large, High School-small, High School-medium and High School-Large).

I can do it as just one big cluster, but do not know how to create the separate clusters.

Any thoughts on this would be greatly appreciated!

Thank you,

Ed

 

[Worf]

Hi Ed

You can create a pivot table from your source table and then produce the corresponding pivot chart.
Place the fields in the following areas:

• Size and grade level on the filters area
• Poverty level and school ID on the rows area(will be the X axis)
• Amount per pupil on the values area(will be the Y axis)

The resulting chart is shown below. Would you like it to explicitly display what size and grade level are currently chosen?
I can post my test workbook if you need it.