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
512 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:

Locking Tables
I am trying to prevent my tables in my publisher document from being re-sized. In other words - I want what I have created to be used as a template that cannot be changed by anyone I send it to. Consequently, I want my cells to be locked (know how to do this) and I want to prevent the users of this template from resizing the rows or columns. I just don't see how you can do this. Anything you can try to do, can be tweaked. -- JoAnn Paules MVP Microsoft [Publisher] "Stan Hunt PMO" <Stan Hunt PMO@discussions.microsoft.com> wrote in message news:8F8AAE24-69E0-41...

Change Pivot Table Field List
I'm looking for an example of how to turn on/off certain fields in a pivot table. What version of Excel do you have? I am using Excel 2007 and here's what I do. Right click anywhere in the pivot table and say "Show Field List". From there I can check or uncheck fields I want or do not want to show. If you right click the pivot table again you can hide that list. "S Himmelrich" <himmelrich@gmail.com> wrote in message news:a54c0a1a-476c-4682-bc5f-95620be56ef6@f20g2000yqg.googlegroups.com... > I'm looking for an example of how to turn on/off certain...

report get parameter information from a table
I have a report that has the recordsource set to a query, the query uses anther query which uses another query that has a date field. I would like the last query to pull the date field from a table, How would I go about doing this? I am running access2003 and this is an .mdb application. Thanks ToniS Assuming that the table that has the date is not in the query already, use DLookup() to get the date. Details in: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - ht...

Creating and updating a Table of Contents in MS Publisher 2003
Last year my department started using MS Publisher to create and update training materials for three call centers. One big drawback (the only one I've found so far!) is that unlike MS Word I have been unable to create an automatic Table of Contents, therefore, we've been creating them manually then updating them each time we need to update the materials (which is frequently). Isn't there some easier way to do this? -- Sharon As you have found out, Publisher does not support TOC. Word is more suitable for this. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http...

Multi-Search results into One Table
Hi All! I'm up for a bit of advice again!! You guys totally helped me out about a year ago! http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/42886/Create-a-Search-using-Surnames-Firstnames-and-Invoice-Numbers#1163066276113601211570m73g2000cwdgooglegroupscom And now I'm up for some help again!! PLEASE!!! I have got a form which I need to search the following data: Surname - Fistname - Invoice Number. The search button I've got at the moment is a search textbox combined with the following query:- Like "*" & [Forms]![Search]![Search2] & "*" Re...

Conditional formatting in a data table
I have conditionally formatted a range of data. When I charted the range the conditional formatting of the data is not included in the data table when this option is selected under the chart. Does anyone know if this is correct or is it me? :confused: Any help would be apprciated. Tim -- TimR ------------------------------------------------------------------------ TimR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27843 View this thread: http://www.excelforum.com/showthread.php?threadid=473518 ...

Excel2007 VBA
If I create a PivotTable with a field called DATE containing dates formatting as dd/mm/yy, I then use this macro to remove Saturdays and Sundays from the PivotTable. Except it reads the dates a mm/dd/yy until they are 'out of scope' and then they are read as dd/mm/yy. Why? Sub HideWeekendsFromPivotTable() Dim pivotName As String Dim pivotDate As Date Dim z, pivotCount As Long pivotName = ActiveSheet.PivotTables(1).Name With ActiveSheet.PivotTables(pivotName).PivotFields("Date") pivotCount = .PivotItems.Count ...

E-mail query to recipients in table
Hello, I have created a database in Access 2003 with a table of suppliers and a table containing my orders with those suppliers. Now suppose I want to send out lists with outstanding orders to those individual suppliers as a sort of reminder, only containing their "own" orders. I have written following code: --- Dim rs As Recordset Set rs = DBEngine(0)(0).OpenRecordset("tblSuppliers") Do While Not rs.EOF If DCount("[SupplierID]", "qryOutstanding", "[SupplierID] = '" & rs.Fields(0) & "'") <...

exporting multiple tables
Hi, I have 6 tables in my database. Depending on which tables the user choses, I want to be able to export those. So if the user picks 4 tables to export, I want to be able to export 4 different text files. Please help. Thank you. Angela wrote: >Hi, >I have 6 tables in my database. >Depending on which tables the user choses, I want to be able to export those. >So if the user picks 4 tables to export, I want to be able to export 4 >different text files. >Please help. >Thank you. How does the user choose the tables? If you use a multi-select list...

How to purge all the Integration Manager log tables?
I went through and purged all the log files from Integration Manager using the procedure outlined in the IM User's Guide. However, after I was finished I looked at the tables in the im.mdb Access database and noticed that the following tables still have quite a few records in them: LogDocumentActivity LogActivity LogDocuments The Log table was empty, which makes sense since I purged all the logs. However, these other tables seem to have retained their records. Is there a way to purge these tables, too? -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 B...

Pivot Chart Bar Width
Is it possible to stabilize the width of the bars when using a Pivot Chart? When there is very little data to chart - based on the values I select from page data pull-downs - the bars are really fat. Other times they are thin. I would really like to have a standard width. tia Pat Watson Pat - The bars are based on a percentage of the spacing between categories. If you have fewer categories, the bars get wider. If you decreased the bar width (by increasing gap width on the Other tab of the format series dialog), they'd be much further apart. You can't adjust these automatical...

How do I compare output data from 2 pivot tables in a graph forma.
I have a pivot table created for 2004 that shows the number of inspections we received on a particular day of the week (ie. 5 on saturdays, 2 on wednesdays, etc.) I am creating the same pivot table for 2005 and am looking for a way to graph the data (bar graph) of the pivot table from 2004 to compare with my 2005 data (ie. last year we received 5 inspections on saturdays, this year we received xx amount on saturdays). Is this even possible to do? I am using Office Pro 2003 and any suggestions would be greatly accepted. Doobi, One option is to combine the pivot tables for 2004 and...

Change Pivot Table Data-Source Connection
I have a fairly huge Pivot Table which source's its data from a SQL Server ODBC connection. I need to change this connection but keep the pivot table - I can't seem to do this. When I click on Pivot table, then the back button to show the list of tables, it won't allow me to go back another time to re-choose the actual ODBC connection? Is this possible? My last resport is to re-create the whole Pivot table again, but I'm trying to avoid this...... Thanks Ben ...

Pivot Table into Report
I'm trying to generate a weekly report for management that summarizes Help Desk activity. I'm using Access DB that links to a CSV, generated from the support system (SQL not accessible). My problem is getting the figures I can generate in the Pivot Table into a form I can just pull up without further editing. I've already separated out by type of request (Break/Fix and Service Request), generating separate queries. What is requested are stats for various service queues(rows), by status (columns), grouped in specific ways; some queues are not grouped, some need ...

Moving attachments between tables in Access 2007
I've got an access 2007 database and some tables with attachment fields defined in them. When I try to move the attachments between tables in VB by just copying the field across I just get an error. I know there just be a simple way of moving them between tables, but I can't seem to find it. Jason ...

typing on both sides of a table/other
Hi! I'd like to make "memory-cards" for my classes, where I put questions on one side of a square-shaped table, and the answers on the other side. I'd like these square shaped parts of the document not too big so it's room for several on one page. Then I can print them, and then cut them making them equal in size. BUT I can't figure out how to type on both sides. Any help here? Thanks in advance. -- krusetruse Insert a table on page 1 and an identical table on page 2. Make sure that margins are equal all round. Set the table rows t...

Pivot Items
Hey guys I ahve a pivot table referencing a large range of data. Because of this I have many available fields to chose from. Most of these fields are not in the actual pivot table but are there. How do I display a list of all the available fields to chose from? Thank you Todd Huttenstine Todd, in Excel 2000 and above, available fields are listed on the Pivot Table toolbar. If you don't see it on screen, click a cell in the pivot table and View > Toolbars > Pivot Table. If necessary, click the Display Fields button on the Pivot Table toolbar. Alternatively, you can click ...

Pivot table seems to round my figures up!
I have a pivot table that works very nicely except for one thing - th data has come through all rounded up e.g. in the data source sheet one column has the following figures: 0.69 0.94 1 but when I create the pivot table it shows all the data as 1 I have formatted all the source cells as number wit 2 decimals and als the numbers cells in the pivot table - now they look like 1.00 I really need to be able to show the 0.69 and 0.94 as well as the 1.00 Is this possible please? Many thanks in advanc -- Message posted from http://www.ExcelForum.com RaeHippyChick, Make sure that you are...

Extract from a table a value by interpolating
Hi, I'm a new with fancy things in excell and would very much appreciate the help. I have a table from which I want to extract values, but I want those values to be interpolated from what is in the table. How can I do that? Thanks, Maria hi, this is how i do it. Lets say your table is at A12 to B20 make another column at column C with this equaition =(B14-B13)/(A14-A13) A B C 12 2.000 1.000 0.5 13 4.000 2.000 1.0 14 6.000 4.000 1.0 15 8.000 6.000 1.0 16 10.000 8.000 1.0 17 12.000 10.000 1.0 18 14.000 12.000 1.0 19 16.000 14.00...

Alternate grey/red shading with subtitles getting shaded differently (Word table)
Dear Experts: I got a telephone list/table with the following make-up: - Each name and corresponding phone number has got its own row. - The letters A-Z also have their own rows and are formatted bold - First name, last name is entered in one table cell - Phone numbers are entered in the table cells next to the "name" cell With any number rows of the table SELECTED would like the table to acquire the following FORMATTING: Rows A, B,C to Z (formatted bold) should have a grey shaded fill (RGB value 133, 133, 133) Rows immediately following these subtitles (A-Z) should...

Data Table
I am working with an Excel Workbook (that I did not create) and there are data tables that arent updating, rather I am just getting #NUM! or #DIVO! errors. The cells in question (those that arent updating) are reading {=Table(,CELL)}. I have tried to use F9 to refresh the table but have had no success. I need to either figure out how to refresh the table or view the table data, neither of which I can do currently. I tried to read the Help section but it suggested I go to the "Layout" Tab, which is not showing up on Excel 2007. Does anyone know how to do solve this issue? ...

look way to map form at CRM frontend to tables at CRM database
How can I find out what tables which new record has been added on when I enter data through a form in CRM front end? I try to find way to map form in the CRM frontend to the tables at the CRM database at backend? It is CRM 3.0 Thank in advances for help! ...

auto filter data within pivot table
Hello, Does anyone know how can we filter data within a pivot table? or sort the data from ascending to decending order withing a pivot table??? Can we do that? It doesnt seems to work?! Thanks. Elaine. You can add fields to the page area, and use them to filter the pivot table's data. To sort a field, select a cell in a column, and click the A-Z button on the Excel toolbar. elaine wrote: > Does anyone know how can we filter data within a pivot table? or sort > the data from ascending to decending order withing a pivot table??? > Can we do that? > > It doesnt se...

training for Pivot Charting in Excel 2000
I am looking for information on Pivot Charting. We use Excel 2000. What actually does pivot charting entail and what would it be used for? NaHa Have a look here at the info provided by Debra Dalgleish on Pivot Charting. http://peltiertech.com/Excel/Pivots/pivotstart.htm Also Debra's own site on Pivot Tables has much good info. http://www.contextures.on.ca/tiptech.html Scroll downpage to Pivot section. Gord Dibben Excel MVP On Tue, 19 Apr 2005 13:22:02 -0700, NaNa at HCMC <NaNa at HCMC@discussions.microsoft.com> wrote: >I am looking for information on Pivot Charting. ...

Excel shift table
Hi, I was wondering if it's possible to create an excel template to do the following. I have 30+ employees working at a shop in a friend's company. He wants to create a template in excel to manage shift duties over a week. when the strength for a shift is given(for example, 8 people on monday morning shift), i want the duties be shared equally (not totally equally) among the staffs. lets say each employee is given a number like emp_Id. is it possible to create a shift timetable like this in excel? ANy help, guides and directions are welcome. Thanks voidmain -- voidmain -------...