If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Analyzing survey results
Hi Everyone,
I am analzying the results of a survey for our company and I am wondering if there is a better (smarter) approach in accomplishing what I want. SCENARIO: I have an Excel spreadsheet full of data from the results of a survey. The first row has field names (A1-J1) A1=Group Name, (ie. HR, IT,), B2= Question 1, C1= Question 2, etc... In the rows just below are random values, ranging from 1-4. 4= Strongly agree, 3= Somewhat agree, 2=Somewhat disagree, 4= Disagree. I want to calculate the % range of answers from each dept for each question. My very very long Solution: I sorted by Group (A1), then ran subtotals to give me the count at each change of group. Next I wrote a Countif formula to calculate the count for the first group(I added 4 rows underneath the subtotal count), then wrote another formula next to this to divide this count to the subtotal count, then formated the # to %. This works fine, but with the amount of groups and questions ahead, if I did this manually it could take some time. Does anyone have another way to approach this? Thanks in advance, |
#2
|
|||
|
|||
Analyzing survey results
You can use a SUMPRODUCT formula to give you the equivalent of COUNTIF
but for two conditions (group and score), and then calculate your percentages from that. You won't have to sort the data or to set up subtotals. I would suggest doing this on another sheet and setting it up like this: Group Score Question1 Question2 Question3 etc HR 1 HR 2 HR 3 HR 4 IT 1 IT 2 and so on. Then in C2 you can enter this formula: =SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!B$2:B $100=$B2)) assuming you have 100 rows of data on Sheet1 - adjust if you have more. Be careful where you put the $ symbols. The formula can be copied across and down to give you the counts of the Group and Score for each question, from which you should be able to derive your percentages in adjacent columns. Hope this helps. Pete On Sep 22, 10:48*pm, Tina K wrote: Hi Everyone, I am analzying the results of a survey for our company and I am wondering if there is a better (smarter) approach in accomplishing what I want. SCENARIO: *I have an Excel spreadsheet full of data from the results of a survey. *The first row has field names *(A1-J1) *A1=Group Name, (ie. *HR, IT,), B2= Question 1, C1= Question 2, etc... *In the rows just below are random values, ranging from 1-4. 4= Strongly agree, 3= Somewhat agree, 2=Somewhat disagree, 4= Disagree. *I want to calculate the % range of answers from each dept for each question. My very very long Solution: I sorted by Group (A1), then ran subtotals to give me the count at each change of group. *Next I wrote a Countif formula to calculate the count for the first group(I added 4 rows underneath the subtotal count), then wrote another formula next to this to divide this count to the subtotal count, then formated the # to %. *This works fine, but with the amount of groups and questions ahead, if I did this manually it could take some time. Does anyone have another way to approach this? Thanks in advance, |
#3
|
|||
|
|||
Analyzing survey results
Does anyone have another way to approach this?
Try pivot table. Eg place Group in ROW area, with Question headers (Q1,Q2, etc) placed in DATA area (one below the other), and with each set to compute Averages (or whatever). In the pivot sheet, just drag "Data" and drop it over "Total" to achieve a columnar view. Tinker to taste. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "Tina K" wrote: I am analzying the results of a survey for our company and I am wondering if there is a better (smarter) approach in accomplishing what I want. SCENARIO: I have an Excel spreadsheet full of data from the results of a survey. The first row has field names (A1-J1) A1=Group Name, (ie. HR, IT,), B2= Question 1, C1= Question 2, etc... In the rows just below are random values, ranging from 1-4. 4= Strongly agree, 3= Somewhat agree, 2=Somewhat disagree, 4= Disagree. I want to calculate the % range of answers from each dept for each question. My very very long Solution: I sorted by Group (A1), then ran subtotals to give me the count at each change of group. Next I wrote a Countif formula to calculate the count for the first group(I added 4 rows underneath the subtotal count), then wrote another formula next to this to divide this count to the subtotal count, then formated the # to %. This works fine, but with the amount of groups and questions ahead, if I did this manually it could take some time. Does anyone have another way to approach this? Thanks in advance, |
#4
|
|||
|
|||
Analyzing survey results
Hi,
Please read up on pivot tables. Your solution may be as simple and straight forward as dragging and dropping -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Tina K" wrote in message ... Hi Everyone, I am analzying the results of a survey for our company and I am wondering if there is a better (smarter) approach in accomplishing what I want. SCENARIO: I have an Excel spreadsheet full of data from the results of a survey. The first row has field names (A1-J1) A1=Group Name, (ie. HR, IT,), B2= Question 1, C1= Question 2, etc... In the rows just below are random values, ranging from 1-4. 4= Strongly agree, 3= Somewhat agree, 2=Somewhat disagree, 4= Disagree. I want to calculate the % range of answers from each dept for each question. My very very long Solution: I sorted by Group (A1), then ran subtotals to give me the count at each change of group. Next I wrote a Countif formula to calculate the count for the first group(I added 4 rows underneath the subtotal count), then wrote another formula next to this to divide this count to the subtotal count, then formated the # to %. This works fine, but with the amount of groups and questions ahead, if I did this manually it could take some time. Does anyone have another way to approach this? Thanks in advance, |
#5
|
|||
|
|||
Analyzing survey results
Hi Pete,
Thank you for the suggestion. But, I'm not quite getting the formula to work. I do not have a "Score" column (yet) as you suggested. Is that a calculation or the possible survey answers? My data looks like this: A1 B1 C1 D1 Group Q1 Q2 Q3 HR 3 3 3 HR 3 4 2 IT 4 3 4 IT 2 2 3 Sincerely, Tina "Tina K" wrote: Hi Everyone, I am analzying the results of a survey for our company and I am wondering if there is a better (smarter) approach in accomplishing what I want. SCENARIO: I have an Excel spreadsheet full of data from the results of a survey. The first row has field names (A1-J1) A1=Group Name, (ie. HR, IT,), B2= Question 1, C1= Question 2, etc... In the rows just below are random values, ranging from 1-4. 4= Strongly agree, 3= Somewhat agree, 2=Somewhat disagree, 4= Disagree. I want to calculate the % range of answers from each dept for each question. My very very long Solution: I sorted by Group (A1), then ran subtotals to give me the count at each change of group. Next I wrote a Countif formula to calculate the count for the first group(I added 4 rows underneath the subtotal count), then wrote another formula next to this to divide this count to the subtotal count, then formated the # to %. This works fine, but with the amount of groups and questions ahead, if I did this manually it could take some time. Does anyone have another way to approach this? Thanks in advance, |
#6
|
|||
|
|||
Analyzing survey results
The score column I suggested is the possible answers that people can
give, so it is just the numbers 1 to 4 in successive cells for HR, then for IT etc. I assumed that this would occupy column B in the second sheet. Put the formula in C2 and copy across and down, and then you will end up with a table like this: Group Score Question1 Question2 Question3 etc HR 1 12 7 15 HR 2 18 16 11 HR 3 15 5 17 HR 4 10 27 13 IT 1 9 12 7 IT 2 14 8 13 IT 3 IT 4 and so on, giving you the count for each question by Group and by Score (or Choice - sorry if the word confused you). Or, you could try pivot tables, as others have suggested. Hope this helps. Pete On Sep 23, 11:11*pm, Tina K wrote: Hi Pete, Thank you for the suggestion. *But, I'm not quite getting the formula to work. *I do not have a "Score" column (yet) as you suggested. *Is that a calculation or the possible survey answers? My data looks like this: A1 * * * *B1 * * * C1 * * D1 * Group * Q1 * * * Q2 * *Q3 HR * * * * 3 * * * * 3 * * *3 HR * * * * 3 * * * * 4 * * *2 IT * * * * *4 * * * * 3 * * *4 IT * * * * *2 * * * * 2 * * *3 Sincerely, Tina "Tina K" wrote: Hi Everyone, I am analzying the results of a survey for our company and I am wondering if there is a better (smarter) approach in accomplishing what I want. SCENARIO: *I have an Excel spreadsheet full of data from the results of a survey. *The first row has field names *(A1-J1) *A1=Group Name, (ie. *HR, IT,), B2= Question 1, C1= Question 2, etc... *In the rows just below are random values, ranging from 1-4. 4= Strongly agree, 3= Somewhat agree, 2=Somewhat disagree, 4= Disagree. *I want to calculate the % range of answers from each dept for each question. My very very long Solution: I sorted by Group (A1), then ran subtotals to give me the count at each change of group. *Next I wrote a Countif formula to calculate the count for the first group(I added 4 rows underneath the subtotal count), then wrote another formula next to this to divide this count to the subtotal count, then formated the # to %. *This works fine, but with the amount of groups and questions ahead, if I did this manually it could take some time. Does anyone have another way to approach this? Thanks in advance,- Hide quoted text - - Show quoted text - |
#7
|
|||
|
|||
Analyzing survey results
Yes, this is very helpful, thanks very much! Thank you to others on the
Pivot table suggestion as well. You guys are great!!! "Pete_UK" wrote: The score column I suggested is the possible answers that people can give, so it is just the numbers 1 to 4 in successive cells for HR, then for IT etc. I assumed that this would occupy column B in the second sheet. Put the formula in C2 and copy across and down, and then you will end up with a table like this: Group Score Question1 Question2 Question3 etc HR 1 12 7 15 HR 2 18 16 11 HR 3 15 5 17 HR 4 10 27 13 IT 1 9 12 7 IT 2 14 8 13 IT 3 IT 4 and so on, giving you the count for each question by Group and by Score (or Choice - sorry if the word confused you). Or, you could try pivot tables, as others have suggested. Hope this helps. Pete On Sep 23, 11:11 pm, Tina K wrote: Hi Pete, Thank you for the suggestion. But, I'm not quite getting the formula to work. I do not have a "Score" column (yet) as you suggested. Is that a calculation or the possible survey answers? My data looks like this: A1 B1 C1 D1 Group Q1 Q2 Q3 HR 3 3 3 HR 3 4 2 IT 4 3 4 IT 2 2 3 Sincerely, Tina "Tina K" wrote: Hi Everyone, I am analzying the results of a survey for our company and I am wondering if there is a better (smarter) approach in accomplishing what I want. SCENARIO: I have an Excel spreadsheet full of data from the results of a survey. The first row has field names (A1-J1) A1=Group Name, (ie. HR, IT,), B2= Question 1, C1= Question 2, etc... In the rows just below are random values, ranging from 1-4. 4= Strongly agree, 3= Somewhat agree, 2=Somewhat disagree, 4= Disagree. I want to calculate the % range of answers from each dept for each question. My very very long Solution: I sorted by Group (A1), then ran subtotals to give me the count at each change of group. Next I wrote a Countif formula to calculate the count for the first group(I added 4 rows underneath the subtotal count), then wrote another formula next to this to divide this count to the subtotal count, then formated the # to %. This works fine, but with the amount of groups and questions ahead, if I did this manually it could take some time. Does anyone have another way to approach this? Thanks in advance,- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|