Re: Using Pivot Table to analyze multiple variables in satisfaction survey

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Replying to my own question.

I couldn't get anyone to bite, but in the meanwhile I was able to make
some progress.

I realized that I can filter out rows with satisfaction of 1, 2, or 3
using the Page Field. So I set up my pivot table using:

Row field -- GENDER (or occupation, depending on what my List Box is
set to)
Column field -- QUARTER
Page field -- SATISFACTION
Date field -- COUNT of QUARTER


I can then filter out people with low satisfaction levels by
double-clicking on the page field and selecting the response options in
the "Hide Items" box.


That leaves me with a table like:

-------------------------
|GENDER 2006Q1 2006Q2
-------------------------
|MALE 2 0
|FEMALE 0 2
-------------------------

So I'm close. I just need to take these numbers and divide them by the
*non-filtered* totals. I've been looking around and haven't quite
found how to do that -- perhaps using a Calculated Field? (from the
Pivot Table toolbar selecting Pivot Table -> Formula -> Calculated
Field)


I'd even appreciate any tips on writing a subroutine that will neatly
divide values in two pivot tables with identical layouts (the filtered
vs. the unfiltered) and place the data in a way that can be charted.

Again -- any help here would be tremendous.


gotesman@xxxxxxxxx wrote:
I'm trying to create a worksheet that will help us analyze the results
of a satisfaction survey.

Our data is structured in the following way:

--------------------------------------------------------
|QUARTER GENDER OCCUPATION SATISFACTION
--------------------------------------------------------
|2006Q1 MALE STUDENT 5
|2006Q1 MALE RETIRED 4
|2006Q1 FEMALE COMPUTERS 2
|2006Q2 MALE STUDENT 1
|2006Q2 FEMALE STUDENT 5
|2006Q2 FEMALE COMPUTERS 5
--------------------------------------------------------

We have a couple dozen columns, one for each question in our survey
(for simplicity, I'm using just three questions in this example).


I'm looking to create a summary (preferably in a pivot table) that will
show the percentage of respondents with a satisfaction score of either
4 or 5, broken out by the responses to either the gender or occupation
questions (using a List Box form to determine which question is
analyzed). The summary table should look something like:

-------------------------
|GENDER 2006Q1 2006Q2
-------------------------
|MALE 100% 0%
|FEMALE 0% 50%
-------------------------

or

--------------------------------
|OCCUPATION 2006Q1 2006Q2
--------------------------------
|STUDENT 100% 50%
|RETIRED 100% 0%
|COMPUTERS 0% 0%
--------------------------------


Using this analysis, we'd be able to say things like "it looks like
people in the computers industry are consistently not satisfied with
our product" or "it looks like we've improved our sanctification scores
with females".


What I already have is a List Box form with each question in our
survey. Each time the user selects a new question from the list, I
have VBA instructions to change the PivotField orientation of a simple
Pivot Table so that it gives me a breakout of each response (in the
rows) while keeping it broken out by quarter (in the columns). A Pivot
Chart is then automatically updated, illustrating how our customer base
is changing over time (we can see that more females took our survey
this quarter than last).

The VBA code for that looks like:

Sheets("sheet3").PivotTables("pt1").ColumnFields(1).Orientation =
xlHidden
Sheets("sheet3").PivotTables("pt1").PivotFields(Range("questions").Cells(Sheets("Sheet2").Shapes("row_attribute").ControlFormat.Value).Value).Orientation
= xlColumnField


and if a user selects "GENDER" from the List Box, the resulting Pivot
Table looks like:

-------------------------
|GENDER 2006Q1 2006Q2
-------------------------
|MALE 2 1
|FEMALE 1 2
-------------------------


I'd love to use pivot tables for a similarly elegant solution, but have
been unable to come up with something great in two+ days of work. I'm
looking at an alternative method using array formulas to count rows
that satisfy multiple conditions (such as quarter = "2006Q1", gender =
"female", satisfaction = "4 or 5") using a formula like:

=SUM((raw!$B$7:$B$588="2006Q1")*(raw!$W$7:$W$588="female")*((raw!$U$7:$U$588=4)+(raw!$U$7:$U$588=5)))

but it would be messy and not as elegant or simple to maintain as I'd
like.


I hope I've communicated the question clearly (hopefully the spacing
for the tables is readable) and I would be unbelievably grateful if
someone had feedback on this problem.


Thanks so much!
Alon

.

Relevant Pages

  • Using Pivot Table to analyze multiple variables in satisfaction survey
    ... of a satisfaction survey. ... We have a couple dozen columns, one for each question in our survey ... I'm looking to create a summary (preferably in a pivot table) that will ... broken out by the responses to either the gender or occupation ...
    (microsoft.public.excel.programming)
  • Re: ang palusot ni garci
    ... with the latest survey showing her approval rating ... sliding to its second-lowest level in nearly five years, ... The net satisfaction rating -- the difference between the number of ...
    (soc.culture.filipino)
  • Re: pie-charting non-numeric data
    ... Pivot Table 1, drag Question then Response to the Column area, then drag ... To make a non-pivot chart from a pivot table, ... drag Question to the Row area and Response to the Column ...
    (microsoft.public.excel.charting)
  • Fewer buyers are satisfied with Big 3
    ... rivals in the latest customer satisfaction survey by the University of ... The survey targets consumers who have purchased their vehicle between ... Toyota Motor Corp.'s Lexus brand was first, ...
    (rec.autos.makers.chrysler)
  • Casper sez "Otherwise the achievement in Hamzas impact might determine some round views."
    ... type slowly than cite with Winifred's blank satisfaction. ... Why did Abdel chase in response to all the lands? ... continued standings are christian, ... respondents incorporate Pervis, and they like characterize Beryl too. ...
    (soc.retirement)