What is the best way to do this - 2nd try

M

MCK

Once again, I have data that I have exported from Excel.
The data is answers to a questionnaire with 27 questions.
The answers have number values and correspond to yes, no,
n/a and blank.

The time period is quarters and there are 30 - 35
departments.

For reporting purposes back to each department, I need to
show how they have scored on the questionnaire with
improvement (or lack of improvement) over time. I would
like a graph for each answer possibility because of the
amount of questions on the survey. So I would have a
separate graph for "yes", "no", etc. for the time period,
1q03, 2q03, etc - each quarter having a data point.

I have tried a pivot table and could not get that to work
for what I need. The more time I spend on this the more
confused I become.

Please help. Thank you.
 
M

mck

columns are as follows:
quarter, department, Q1, Q2, Q3, etc. up to Q27(answers to
the questions)

thanks for your assistance.

mck
 
M

Myrna Larson

To get a pivot table to work well, you'd need to change your data so the
columns are Quarter, Dept, Question Number, and Answer. In other words, for
each row you have now, there would be 27 rows rather than 27 additional
columns.

Then you could put, e.g., the Department and Question number as page fields,
Quarter as a column field, Answer as a row field, Answer as a data field. The
data field would summarize by Count rather than sum.
 
M

mck

OK, I'm not quite sure I understand this....the question
numbers would be rows as well as columns? And then for
answer would I have a column for each possible answer?
 
M

Myrna Larson

No. 4 columns. First contains the Department name (sales, etc), 2nd the
Quarter (a number 1-4), 3rd the question number (a number 1-27), and Answer
contains the answer for that question for that department and quarter.

So for one department, 1 quarter you would have 27 rows, each with 4 columns,
instead of 1 row with 28 columns.

Your present layout is in essence a sort of pivot table already, since you
have one column for each question.
 
M

Myrna Larson

For each combination of department, quarter, and question, you have one row.
If there are 30 departments, 4 quarters, and 27 questions, that's 3240 rows.
Each row identifies the department, quarter, question number, and answer. If
the answers are numbers 1-10, a row would look like this

Sales 1 13 8

meaning sales department, 1st qtr, question 13, rating 8.

If the Answer is a number, you could use

Page Field: Department
Column Field: Quarter
Row Field: Question
Data Field: Average of Answer

If Answer is text, you need to put it in as a row field, also, so you get
counts for each possible answer. Or, use some sort of translation table
(VLOOKUP) to convert the text answers to numbers.
 
Top