How to Chart Multiple Variables & Effectively Organize the Data

[YPJunk]

Hello,

Hope all is well. I am collecting data on exam percentile ranking and scale scores for each student in two different periods. So the inputs are:

• the percentile ranking
• scale score
• Students
• Period (currently Spring 2013, and Spring 2014)

I need to be able to see the ranking/scale for each student and the average ranking/scale score the entire class per period. This information will come from the teachers, and I need to figure out the most effective way I can organize the form so in turn I can create the graph.

Attached is an example with comment. Could anyone assist?

Thank you in advance!

-YP

 

[wchaug]

YP,

I have taken a look at your spreadsheet and pivot chart, and have made a suggestion on the 3rd tab.

First of all, I put the data into a table. Home Ribbon -> Format as Table. I really like tables because creating pivots from them is a snap.

Then I created a pivot chart using the table as the source. Based on the file you've attached, you seem to know what a pivot chart is and how to use one, so I'm not going to go over it.

Unfortunately, as you have mentioned in your 1st attempt it may be tedious because you didn't want to have two records for each student. I've made it worse because now I have 8 records for each student. Normally I don't recommend them, but a simple userform could help with this.

I hope this helps. By no means am I a master of pivot tables or pivot charts so maybe a ninja could help further...

 

[Balli]

Hello YPJunk,

Is this workable for you? Please go through attachment.

 

[YPJunk]

Thank you!