I have questionnaire data laid out horizontally like this:

Session Session1
Question1 Question 2
Person
1 1 4
2 3 5

The numbers lining up with Question1 and Question2 are the numeric responses
of
Person1 and Person2. There are about 16 sessions and each one can have a
differing number of participants. There are 10 questions for each session.

In order to get a pivot table that aggregates each session and gives the
average for each question in each session, I had to re-arrange the data
vertically like this:

Session Person Question Answer
session1 1 1 1
session1 1 2 4
session1 2 1 3
session1 2 2 5

My pivot looks like this:

SESSION PERSON QUESTION1 QUESTION2
session1 1 1 4
2 3 5
SESSION1 TOTAL 2 4.5

(that last line is the average of all the answers to the question)

My question is: was it really necessary to arrange the data vertically or
could the pivot have been done off the original data structure? I just
couldn't get it to work.

If the answer is "yes, the data needs to be arranged vertically", then
could someone suggest an easy way to re-arrange the data which is already
entered
horizontally on a sheet in the workbook? It can all be rekeyed if necessary
but I'd like to avoid it.

Lastly, if there is an online resource that addresses these questions, feel
free to point me to it. I wasn't able to quickly find anything in the great
mass of Excel tips on the web.

Thanks,
gary

Hi Gary

On your Pivot Table, try double clicking on the Grand Total cell at the
bottom Right of the PT.
On the sheet that open up, does this now have the data in the format you
want?
If so, just rename the column Headings, from Row and Column to what they
should be, and perform a new pivot based upon this data.

--
Regards

Roger Govier


"gary" wrote in message
...
I have questionnaire data laid out horizontally like this:

Session Session1
Question1 Question 2
Person
1 1 4
2 3 5

The numbers lining up with Question1 and Question2 are the numeric
responses
of
Person1 and Person2. There are about 16 sessions and each one can
have a
differing number of participants. There are 10 questions for each
session.

In order to get a pivot table that aggregates each session and gives
the
average for each question in each session, I had to re-arrange the
data
vertically like this:

Session Person Question Answer
session1 1 1 1
session1 1 2 4
session1 2 1 3
session1 2 2 5

My pivot looks like this:

SESSION PERSON QUESTION1 QUESTION2
session1 1 1 4
2 3 5
SESSION1 TOTAL 2 4.5

(that last line is the average of all the answers to the question)

My question is: was it really necessary to arrange the data vertically
or
could the pivot have been done off the original data structure? I
just
couldn't get it to work.

If the answer is "yes, the data needs to be arranged vertically",
then
could someone suggest an easy way to re-arrange the data which is
already
entered
horizontally on a sheet in the workbook? It can all be rekeyed if
necessary
but I'd like to avoid it.

Lastly, if there is an online resource that addresses these questions,
feel
free to point me to it. I wasn't able to quickly find anything in the
great
mass of Excel tips on the web.

Thanks,
gary