Hi All,

I have a Questionnaire with 2 parts (Part A and Part B), and each of the two parts have the exact same questions (31 questions each). Essentially, the 31 questions in Part A ask the user "the *importance* of each issue", while the similarly worded 31 questions in Part B asks the user "how well you think we are doing".

The scoring (or answers prompted of the user is a scale of 1 to 5, with 1 being HIGH IMPORTANCE/EXCELLENT/STRONGLY AGREE" and 5 being LOW IMPORTANCE/POOR/STRONGLY DISAGREE.

Now, what I'd like to obtain is the "gap" between the "importance" portion of each answer vs. the "how well we are doing" portion of the same answer, from Part B lower down.

On my rawdata sheet, and pertaining to just one submission form, the way the raw data is laid out is as follows (without the pipe "|" characters of course);
================================================
Part | Q. No. | Question | Answer
================================================
A | 1 | Administration (co-ordination of staff etc.) | 1
A | 2 | Music | 3
A | 3 | Traditional Service | 2
.....
.....
A | 31 | Social Events | 3
B | 1 | Administration (co-ordination of staff etc.) | 4
B | 2 | Music | 1
B | 3 | Traditional Service | 2
.....
.....
B | 31 | Social Events | 4


So essentially what I've done is I've built a PivotTable with the question number and the question in the "row labels" area, and the "Part" in the "column labels" area, and finally the answer in the "values" area.

So the way the data shows in the PivotTable is:
==================================================
Q. # | Questions | A | B |
==================================================
1 | Administration (co-ordination of staff etc.) | 1 | 4 |
----------------------------------------------------------------------------------------------------
2 | Music | 3 | 1 |
----------------------------------------------------------------------------------------------------
...and so on.

So from the PivotTable above (or alternatively if there is a better/different way to do this) what I'd like to obtain is....for each question, I'd like to highlight/caclulate the gap between the importance to the user, and how well we are doing in that area.

So example row/question # 1 in the pivottable above says that it's of high importance to the user (Part A value/answer = 1), where as we are doing very poor in that area (Part B value/answer = 4). So there is a gap of 3, and that should be red-flagged, for us to take action on.
A reverse of that i.e. row/question # 2 would be that the question is of low importance to the user (Part A=3), however we are doing Excellent in that area (Part B=1)...and that's a green flag right there.

Now, the question I have is this: for each question/row in the PivotTable, how can I get the gap/difference between the value under Part A, and the value shown under Part B?

Again, if a PivotTable is the wrong approach then appreciate letting me know how to approach this problem.

Thanks,


C