Thread: Pivot Table question

Hi
I have created a database in access for recording results of our local parish questionnaire. I'm analysing the results as a Pivot table in Excel.

The data looks like this (a snapshot of question 9 responses)
AgeGroup DescriptionQuestionID
25 - 34 a 9
25 - 34 b 9
25 - 34 c 9
25 - 34 Other 9
25 - 34 a 9
25 - 34 b 9
25 - 34 d 9
35 - 44 d 9
35 - 44 e 9


So the table has the "agegroup" in the row area, the "description" in the column area and I’m counting the questionid's so I get

Count of QuestionID Description
AgeGroup Other a b c d e Grand Total
25 - 34 1 2 2 1 1 7
35 - 44 1 1 2
Grand Total 1 2 2 1 2 1 9


What I was trying to show is what is the percentage of responses to that question against the total number of people responded. So I thought that will be easy as well. I know how many people responded so I’ll just create a calculated field that takes the count of the questionid/ [no of responses]


So for example if 2 people have responded with answer "More visible police presence" out of 10 people who filled out the questionnaire it should be 20%... Well I can't get it to work inside the pivot table.

If my new calculated field (field1) is =COUNT(QuestionID) /10
It just displays
Description
AgeGroup Data Other a b c d e Grand Total
25 - 34 Count of QuestionID 1 2 2 1 1 7
Sum of Field1 0.1 0.1 0.1 0.1 0.1 0.1 0.1
35 - 44 Count of QuestionID 1 1 2
Sum of Field1 0.1 0.1 0.1 0.1 0.1 0.1 0.1
Total Count of QuestionID 1 2 2 1 2 1 9
Total Sum of Field1 0.1 0.1 0.1 0.1 0.1 0.1 0.1

As opposed to taking the count of the question in each of the columns and displaying what I want

It works quite happily outside the table by doing the simple calculation but that’s no good as I have 60 questions and want to produce a page for each one

Am I missing something obvious?

Thanks for you time

Perhaps you can add a column in the General Query tab that does the calcs and then create Pivot Table to include that item

Can we see an example of what you've done?

see attached... Sheet2... is this what you need?

The Sumproduct() formula here counts number of items that match multiple criteria...

i.e. =SUMPRODUCT(--($B$2:$B$187=B2),--($C$2:$C$187=C2))

counts how many rows with row 2 and 187 column B = B2 and column C = C2 (much like Countif()) but allows for 2 or more criteria.... then that sum is divided by D2 to get the final average...

the -- coerces the TRUE/FALSE results obtained by the conditions to 1/0 respectively so that the math can be performed and sumproduct functions as designed.