surveys and pivot tables

I have reinvented the wheel by making a paper survey and then entering all 
the answers into a spreadsheet. the format of the spreadsheet is; the first 
column is the id of every person surveyed and the next 9 columns are their 
response to each of the 9 questions (responses are numbered so each response 
is a number from 1-8)each row is in reference to the answers that the person 
gave. so a typical row would be: PCA 1 6 1 2 2 1 5 8 1. pca being the ID and 
the other numbers being the response to the corresponding row's question. 
The question is: how do i set up a pivot table to analyze the results. 
please help me! (i can get the wizard started but i never end up with a 
legible table)
-- 
thanks in advance
0
jcontrer (2)
10/30/2007 2:42:02 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
501 Views

Similar Articles

[PageSpeed] 14

Hi,

Enter a row above the data with headings for each column. e.g.
ID Q1 Q2 Q3...

Then you will have something to Pivot.
In the PivotTable, drop Q1 and Q2 in the Data area. Then click on 'Sum of 
Q1' and change it to average.

This seems a pretty trivial application for a Pivot Table. So perhaps you 
should consider using worksheet formulas.
As in
=AVERAGE(B2:B40)
=STDEV(B2:B40)
etc.

Ed Ferrero
www.edferrero.com


>I have reinvented the wheel by making a paper survey and then entering all
> the answers into a spreadsheet. the format of the spreadsheet is; the 
> first
> column is the id of every person surveyed and the next 9 columns are their
> response to each of the 9 questions (responses are numbered so each 
> response
> is a number from 1-8)each row is in reference to the answers that the 
> person
> gave. so a typical row would be: PCA 1 6 1 2 2 1 5 8 1. pca being the ID 
> and
> the other numbers being the response to the corresponding row's question.
> The question is: how do i set up a pivot table to analyze the results.
> please help me! (i can get the wizard started but i never end up with a
> legible table)
> -- 
> thanks in advance 

0
ed1304 (117)
10/31/2007 10:42:32 AM
Well Ed, 
The survey is one where i'm trying to find and rate satisfaction of 
employees with a service that we offer them. An example of the data table 
that i am trying to set up is one that would tell me: how many people 
answered choice 1 of question one, and so on for all 9 questions. But then i 
would also like the pivot table to tell me how many people chose choice 1 to 
question 1 AND chose choice 5 to question 7. I hope that gives you a better 
idea of what i'm trying to do, i dont think standard deviation and average 
would help me because the results are different per question. the only reason 
for the numbers is so i can get data. but the info was helpful.
 
-- 
thanks in advance


"Ed Ferrero" wrote:

> Hi,
> 
> Enter a row above the data with headings for each column. e.g.
> ID Q1 Q2 Q3...
> 
> Then you will have something to Pivot.
> In the PivotTable, drop Q1 and Q2 in the Data area. Then click on 'Sum of 
> Q1' and change it to average.
> 
> This seems a pretty trivial application for a Pivot Table. So perhaps you 
> should consider using worksheet formulas.
> As in
> =AVERAGE(B2:B40)
> =STDEV(B2:B40)
> etc.
> 
> Ed Ferrero
> www.edferrero.com
> 
> 
> >I have reinvented the wheel by making a paper survey and then entering all
> > the answers into a spreadsheet. the format of the spreadsheet is; the 
> > first
> > column is the id of every person surveyed and the next 9 columns are their
> > response to each of the 9 questions (responses are numbered so each 
> > response
> > is a number from 1-8)each row is in reference to the answers that the 
> > person
> > gave. so a typical row would be: PCA 1 6 1 2 2 1 5 8 1. pca being the ID 
> > and
> > the other numbers being the response to the corresponding row's question.
> > The question is: how do i set up a pivot table to analyze the results.
> > please help me! (i can get the wizard started but i never end up with a
> > legible table)
> > -- 
> > thanks in advance 
> 
> 
0
jcontrer (2)
11/1/2007 7:34:00 PM
Reply:

Similar Artilces:

ms sql # temp table
vb.net 2008 I have a MS SQL stored procedure that uses # temp tables in the following manner: create #counties (countyname varchar(20)) insert into #counties ...... select firstname, lastname, address from personlist pl inner join #counties c on pl.county = c.countyname A string of countys is passed into the procedure and turned into a temp table for filterin the personlist. I've been using this strategy for years and it's always worked fine. I use these procs as the source for MS Access reports, Delphi reports, etc. Never a problem. Now I'm trying to create a ...

Index & Match on Pivot Table
Hi, I'm using Office 97 - and was wondering if there is a way to do an Index (Match), (Match) type function on a Pivot Table? I've tried it - but I get a #value error. Does anyone know a way around this? Nelson Nelson wrote: > Hi, > > I'm using Office 97 - and was wondering if there is a way to do an Index > (Match), (Match) type function on a Pivot Table? > > I've tried it - but I get a #value error. Does anyone know a way around > this? > > Nelson > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextur...

No Group dialog box appear when working in Pivot Table
I'm trying to group a range of dates in pivot table. According to help files a dialog box should appear when selecting Group..., but nothing happens except a grouping of the entire marked range. How do I go about in order to make the Grouping dialog box show? Best Regards PerD > PerD, This might be something particular to my set-up (XL2K), but I have to build the table initially by specifying the data range *exactly* (click'n'drag on the worksheet) for grouping dates to be an option. If I use a blanket source (eg: A:B) it won't cut it. Try it again from scratch, the...

How do you get a TRUE refresh of a Pivot table
Oh Pivot tables are so good and boy, do they save on calc time and size of file! BUT.........I notice that if some of the data it relies on is changed, by some magic, it still remembers the old data and, that old data will also be shown in the table unless you physically delete those references using the drop downs. I have found that sometimes you can clear the old references by re-running the wizard backwards as it seems to refresh those references. But that doesn't work in all situations. Is there a way to remove old references automatically AND how is it possible that they still exist?...

Sharing only forms, not tables
I have an access database. I have designed several forms which shows information retrieved by querries. I would like to share this forms with my colleagues via server. But, i dont want my colleagues to access tables and querries, because of security reasons. Could you pleae tell me a good reference to do that? How can i keep my tables in a seperate place than forms. Thanks That's not going to work. Forms have no data of their own. They read their data from and write their data to tables. If your users have not rights to read or write the tables, your forms won't work either. -- A...

formula in a pivot table?
Hello guys, is that true that excel-pivot is only able to sum (meanvalue is no selectable) from a formula made table. usually you put by drag and dro the data field into your table. but you also can put by hand a formula which is quit useful sometimes. after lunching the data-field in your pivot you can say pleasy show me the minimum or the meanvalue. It looks like that is not possible when you create a data-field using formula. then excel is just able to sum that ****... do you have any other idea?? cheers, filipu -- filipu ------------------------------------------------------------...

Pivot tables- 0 counts don't display
How can I get a 0 counts to display in the pivot chart and subsequent graphs. Thanks to Debra for answering my last question, it was a big help! You could enter a dummy record for each combination in the table, and leave the door field blank. For example: door floor core doortype 1 1 1 2 1 1 3 1 1 These records will be displayed in the column and row area, but won't be counted in the data area, where 'Count of Door' is used. Jim wrote: > How can I get a 0 counts to display in the pivot chart > and subs...

Problem with Word table in Excel
Hope someone can help. Here's my problem. I want to easily double the amounts in each row of a column in a Microsoft Word table. I exported the table to Excel, inserted a column (column B) next to the one I wanted to work with, and typed the formula =A1*2. I then copied it to the rest of the cells in the B column. Of course, it worked. All the numbers in the A column were doubled in the corresponding B-column cells. But when I went to copy the doubled B-column numbers into the A column, I got a circular reference error. Problem is that I'm copying the formula, not the actu...

question on tables and queries
I've imported 2 tables from excel and ran a query against those two tables to determined the difference. This is something I would like to do each month. I would like to keep the same settings in my database, is there a way I can easily import/update the data in the new table without creating a new one each month? The query would stay the same as I'm comparing the same two files, but the the data will be changing every month. I don't know much about access and would like to know if there is an easier way of doing this besides creating a new table and query each month? I&...

Pivot Tables: New dimension members show up checked in dropdown
I've noticed that when distinct new members are added to dimension dat they show up as checked in a dropdown list when the pivot table i refreshed. Is there anyway to disable this so that they don't show up (pre checked? Basically, I have a model that depends on a subset of the members which I have checked in a dropdown, and the rest are unchecked - an when a distinct new member is added it automatically shows up a checked in the dropdown list, which gives me wrong answers. E.g. I have Stock Tickers as a dimension and avg. price as the measure I have 10,000 Distinct Tickers of whic...

Yes/No dropdown and table field validation
Good Morning,I'm currently working on putting a 'yes/no' dropdown box in my Accessform, without table lookup for the 'y/n' values. I had read that thetable field for this could be 1 char long, with a validation rule of:'in("Y","N")'. However, I get a datatype conflict when I test theform. When I take out the validation rule, it works seeminglyseamlessly. Is the validation rule actually needed, and can it bemade to work?Thanks,Louis A Yes/No field stores -1 for Yes (true), and 0 for No (false.)The text "Y" and "N" are not t...

Pivot table field settings--ghosted out
I'm MS-Excel 2003 pivot tables which connect to an OLAP database. I a trying to set my subtotals in the pivot table to "AVERAGE". Currently they're defaulting to the "SUM" setting. I right click on the cell (I do this for both the cell and the cel heading...same result) and select "FIELD SETTINGS". This brings up th Pivot table field window. The only options that I have that are no ghosted out are "AUTOMATIC" and "NONE". I understand that I need to tick the CUSTOM radio button under th Subtotal heading, but i am unable to do so...

Searching documents with tables
Not sure if Windows 7 issue or word issue, but I am trying to search for text in word document(s) that is in a table. In Windows Explorer I type the test in the search box and hit enter. If the text is not in a table windows search finds it perfectly, but words that are in a table it won't find. Is there a way around this? ...

What is the quickest way to copy a table to an array?
Hi everyone, I have a table to process. It can vary in size between 2-4 columns and 2-100 rows. The table is a simple thing, containing just plain text. What is the quickest/nicest way to copy the content of the table to a 2D array? Stephenc stephenc wrote: > Hi everyone, > > I have a table to process. It can vary in size between 2-4 columns > and 2-100 rows. The table is a simple thing, containing just plain > text. > > What is the quickest/nicest way to copy the content of the table to a > 2D array? > > Stephenc I would do this in th...

Table of Contents not displaying
I have been succssfully using the Table of Contents feature forever. Suddenly, it displays only codes on the screen, yet will properly print and will display properly in "Full Screen Reading" mode. Creating a PDF also displays the ToC correctly. This occurs only on my computer and will display properly on other computers. How do I fix this? Press Alt+F9 to toggle the display from field codes to field results. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "tomMedClinic" <tomMedClinic@discussion...

How do I distinguish between upper and lower case with pivot tabl.
How do I distinguish between upper and lower case items when creating a pivot table, e.g. a list of warehosue names which have combinations of both upper and lower case, lower case being one type of warehouse and the upper case ones representing a different type What you could do is add a column to the original table that attaches a "1" in front of every warehouse name whose first character is upper case. =IF(CODE(LEFT(A2))<91,1&A2,A2) And fill down. Then use this new column in your pivot table. HTH Jason Atlanta, GA >-----Original Message----- >How do I dist...

Pie chart handles in the pivot table don't function
Hi All: I have a pie chart which activates off a pivot table. I want to expand the pie chart size so that I can see it better, and I can't get the "pull" handles to work? Please advise. Really need this one answered right waay, as I am missing a deadline! Thanks, Hazel Hi, I don't believe you can resize the plotarea for a pie chart, or in fact any chart, when it is linked to a pivot table. You will need to make your own chart based on the cells. But don't you the mouse to select the cells otherwise it will create an automatic link to the pivot table and ...

Changing Linked Table Name
Access 2003 Every once in a while I decide to change the name of a table in a standard fe/be setup. Occasionally the name change encompasses a number of related tables. The process is... 1) go to the BE and change the actual table name 2) go to the FE and relink the changed tables 3) go to the queries and reconstruct any related queries 4) go to the forms and reselect the reconstructed queries/tables 5) go to the modules and run a find/replace Obviously, this is quite time consuming. I don't expect to be able to change anything about step 5, but is there some easier w...

Calculated Field in Pivot Table #3
Anyone know how I can edit or delete a calcualted field once I've created it? Go to the "Insert Calculated field" dialog. In the "Name" dropdown, select the field that you want to delete, then click the "Delete" button. -- Regards Juan Pablo Gonz´┐Żlez "cchristensen" <cchristensen@discussions.microsoft.com> wrote in message news:866D80D1-4C2C-4489-9C86-52DB8A3023BE@microsoft.com... > Anyone know how I can edit or delete a calcualted field once I've created it? ...

where can i find my tables in form design?
I'm creating my form from design view and didn't use the wizard. I tried to look for my tables and can't seemed to find them anywhere in the screen. Where can i find it? View, properties, Data Tab, and pull down record source. If the field list box isn'tdisplayed you will need to select View, Field List. Hth Jon <emerlita@shaw.ca> wrote in message news:1176390153.055871.148970@d57g2000hsg.googlegroups.com... > I'm creating my form from design view and didn't use the wizard. I > tried to look for my tables and can't seemed to find them anywhere i...

Pivot Table view of Union Query
Dear All, I can see what I need at the Pivot Table view of a Union Query. However, this cannot be correctly exported to Excel. Although there is a function of "Export to Microsoft Excel", however, all "detail data" in Access was gone - leaving only the count of the data in Excel. How can I show all these detail data in the data area of the Pivot Table? Alternatively, is it possible to copy the Pivot Table in Access and then paste in Excel? I cannot see any Select Rows commands in the pull down menu. Thank you very much. Hong -- Message posted via AccessMonster.com h...

Excel to Access linked tables
I want to create several linked tables (or imported data) in an Access database which are linked to several password protected (and shared, though the sharing could be removed if I can get this to work)) Excel spreadsheets. I have linked to the ones which are not protected ok, but I can't find a way of doing this when they are password protected, even though I created the spreadsheets and protected them and obviously know all the passwords. Any help or ideas would be greatly received. Thanks ...

Merging Workbook Table data Based upon Value comparisons
I have two workbook tables (Two different workbooks) with two matching column names. What I wish to do is to merge values from one table to another, but ONLY for those records inwhich these two columns have matching values. Would this be possible? Jay Are you saying you have two workbooks, or are the tables within one workbook? "jayceejay" <jayceejay@discussions.microsoft.com> wrote in message news:AC73B2C7-83EF-4D27-A464-32AEEE7D4214@microsoft.com... >I have two workbook tables (Two different workbooks) with two matching >column > names. What I wish to do ...

One form, two tables UPDATE help?
I am running Access 2003. A user enters data into one field on a continuous form bound to one table. I want to be able to UPDATE this same data into a similar field in another table based on an equal week number, year and Part number for the data. Is this possible? I have been trying something like: Sub UpdateCM1() Dim strQuery As String Dim HoldWeekNo as Integer Dim HoldYear as Integer Dim HoldPartNo as String HoldWeekNo = Forms!frmMnthCMNew!WeekNo HoldYear = Forms!frmMnthCMNew!Yr HoldPartNo = Forms!frmMnthCMNew!PartNo strQuery = "UPDATE CMMonth " & _...

how do i stop both pivot toolbars popping up everytime a pivot is.
how do i stop both pivot toolbars popping up everytime a pivot is refreshed or range is changed? ...