Excel to chart or graph survey results

[herrld]

I've been asked to help analyze customer service survey results.
Here is a sample of the data.

Q4 - Q9 relate to specific questions that the customer answers. They can score Q4-Q8 as 0,3,6,9,12. 0 being Unsatisfactory, 12 being Excellent.

They can score Q9 as 0,10,20,30,40. 0 being Unsat, 12 being Excellent. Obviously Q9 is the most important question because it is given more weight.

The totals of Q4-Q9 = the Tot column.

ID Customer Date AssignedTo Q4 Q5 Q6 Q7 Q8 Q9 Tot
1 Flintstone,F. 05/17/2011 Superman 12 12 12 9 12 40 97
2 Rubble,B. 05/17/2011 Batman 12 12 12 12 12 40 100
3 Flintstone,W. 05/17/2011 Robin 12 12 12 12 12 40 100
4 Flintstone,P. 05/16/2011 Spiderman 12 12 12 12 12 40 100
5 Rubble,B. 05/16/2011 WWoman 12 12 12 12 12 40 100
6 Rubble,BB. 05/16/2011 Aquaman 12 9 12 12 12 30 87
7 Brady,M. 05/14/2011 Flash G 12 12 12 12 12 40 100
8 Brady,J. 05/14/2011 LoneRanger 12 12 12 12 6 0 54
9 Brady,C. 05/13/2011 Wolverine 12 12 12 12 12 40 100
10 Brady,B. 05/13/2011 Underdog 12 12 12 9 12 30 87
11 Brady,G. 05/13/2011 Incr Hulk 12 12 12 12 12 40 100
12 Brady,C. 05/13/2011 Grn Hornet 12 12 12 9 9 40 94
13 Brady,M. 05/13/2011 Power Rgr 12 12 12 12 12 40 100
14 Brady,P. 05/13/2011 Spiderman 3 3 3 3 0 0 12
15 Flintstone,F.05/13/2011 Flash G 6 6 6 9 9 30 66

I'm not a statistical analyst nor an Excel wiz so I need some help on how to get started. The one thing that I do know is that the "assigned to" individual is not terribly important as they are mostly temps. We just want to see how we're doing with each question Q4-Q9 over the given time frame. I think probably by year and by quarter.

Thank you!


Linda

 

[SkipVought]

hi,

over the given time frame. I think probably by year and by quarter.

What is 'the given time frame?'

What story do you want your chart to tell?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rofeu]

One option is calculate the average and standard deviation per period and display the average as a line in a graph using the standard deviation as the + and - Y-error bars.

Another option is to count the number of times a particular score is present for a given period and display score-count against the score (perhaps group the scores per 5 or something depending on your spread) in a histogram.

Check out pivottables and pivotgraphs in Excel. They are very useful for these purposes.

Cheers,

Rofeu

 

[herrld]

Skip, the surveys can be pulled from the beginning of time (Aug 2008) to current. I think originally they will want to see all the data broken down into Year and Qtr but ongoing it would just be either Calendar Year and Qtr or Fiscal Year and Qtr.
I haven't been given any guidance on this from mgt but I believe that to get a good picture of the satisfaction of our customers on each of the questions that they want to see how many times they receive a score of each of the possible values (0,3,6,9,12 for Q4-Q8) and (0,10,20,30,40) for Q9 per question of course. And then also see how the scores each question rank as a percentage of the total score. Does that make sense at all

Linda