Calculating survey results

**Stacy Boyer** · 07-19-2011, 02:38 PM

I am sure that I am making this more complicated than it needs to be.

I have a survey that I am needing to capture how the associates are responding to each question. There are 52 questions that need to be analyzed, and I need to know how each department, age group or gender is responding to these.

I am not sure if I need to be using "IF statements" or trying to build a pivot table or what.

Any help would be appreciated.

Thanks--

**Andy Pope** · 07-20-2011, 03:48 AM

I would use a pivot table. See attached.

But first you need to modify your data, I have done the first question to illustrate.

**Stacy Boyer** · 07-22-2011, 10:02 AM

Originally Posted by Stacy Boyer

I am sure that I am making this more complicated than it needs to be.

I have a survey that I am needing to capture how the associates are responding to each question. There are 52 questions that need to be analyzed, and I need to know how each department, age group or gender is responding to these.

I am not sure if I need to be using "IF statements" or trying to build a pivot table or what.

Any help would be appreciated.

Thanks--

I modified the data, and put the information at the end of the sheet, instead of at the beginning. I am not getting the disired information. When I insert the pivot table it is giving me the percentage of the total number of lines instead of the number of surveys given. Any suggestions?

**Andy Pope** · 07-22-2011, 10:19 AM

Hard to say without seeing what you have done but have you set the Value field setting of the table to be % of Column?

**Stacy Boyer** · 07-22-2011, 10:39 AM

Originally Posted by Andy Pope

Hard to say without seeing what you have done but have you set the Value field setting of the table to be % of Column?

The attachment is to big, and won't let me post it...any suggestions?

I do have the field setting to be a % of the column. But it is totallying the number of lines in the column not just the number of survey lines. Does that make sense?

**Andy Pope** · 07-22-2011, 10:58 AM

reduce the number of records in the file such that the problem is still present.
zip the workbook.

**Stacy Boyer** · 07-22-2011, 11:36 AM

Originally Posted by Stacy Boyer

The attachment is to big, and won't let me post it...any suggestions?

I do have the field setting to be a % of the column. But it is totallying the number of lines in the column not just the number of survey lines. Does that make sense?

Thank you--

**Andy Pope** · 07-22-2011, 12:07 PM

Not sure why but you have included all the lines at the bottom of the data table.

Currently using Sheet3!$EF$2:$GA$465

should be using Sheet3!$EF$2:$GA$323

You can use the PT Wizard and step back and change the data source address

**Stacy Boyer** · 07-22-2011, 01:21 PM

Originally Posted by Andy Pope

Not sure why but you have included all the lines at the bottom of the data table.

Currently using Sheet3!$EF$2:$GA$465

should be using Sheet3!$EF$2:$GA$323

You can use the PT Wizard and step back and change the data source address

Andy,

Last two questions.
1. Is there a way to make the pivot table automatically update when new data from surveys are added to it?
2. Is there a way to make the pivot table automatically format the table to the percentage of the column, without telling it to for each question?

thanks for evreything. I am fairly new to pivot tables, and need all of the advice I can get!

**Andy Pope** · 07-23-2011, 05:16 AM

You could use a dynamic named range for the data soure reference.

Probably require VBA to automatically format the table when the row variable is changed.

See here for pivot table related articles.
http://www.contextures.com/tiptech.html#P