 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2005advice request
Hi, I'm looking for a little advice. I have volunteered to use Excel to analyze the results of a big bunch of surveys that some teenagers from the local school have created and answered. And I'm looking for your advice on how to go about it, before I get started down the wrong track. The survey is about 35 questions long. It's mostly multiple choice questions (~90%). There are between 2 and 10 multiple choice answers to pick from on these questions. About 10% of the questions have some type of open ended text responses (2 lines at most for these). We have agreed to use Excel to compile and analyze the data so as many people as possible can work with the data. There are about 700 survey sheets to enter and analyze (each sheet has the 35 questions). The data entry will be done by about 10 different volunteers with various versions of MS Excel (both Windows & Mac). My task is to: 1) create an Excel template that I can email to the 10 data-input volunteers so they can keyboard the data from the survey sheets into the template. 2) Receive by email the 10 different Excel data files that the volunteers will create from my template. 3) compile/merge the 10 Excel files into one master file. 4) Be able to analyze and present the data from the 700 survey sheets upon the community's request. I was thinking that I would create a template that has the 35 questions and possible multiple choice responses on the first sheet of the Excel page and then try and duplicate that 1st page for each of the 700 surveys, but it doesn't seem like a very elegant way to handle this. Can you think of a better way to do it? Your help would be greatly appreciated. All of the participants have volunteered their time and effort. No one will benefit financially from this project. thanks, rgraham1 One approach, for the multiple choice questions at least, might be to simply have Column A reference the questions, and have each adjoining column reference an individual questionnaire. With 700 surveys split among 10 volunteers, that makes just 70 columns necessary for your template. With just 35 questions, you could start your tally formulas at the bottom. Something simple, such as counting the number of the multi-choice answer (1, 2, 3, ... etc.). =COUNTIF(B1:BS1,COLUMN(A:A)) Copy across 10 columns ( max number of choices), And down 35 rows (max number of questions) You'll have to exclude the text answers of course. Then just total the 10 templates.  SignatureHTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== Hi, I'm looking for a little advice. I have volunteered to use Excel to analyze the results of a big bunch of surveys that some teenagers from the local school have created and answered. And I'm looking for your advice on how to go about it, before I get started down the wrong track. The survey is about 35 questions long. It's mostly multiple choice questions (~90%). There are between 2 and 10 multiple choice answers to pick from on these questions. About 10% of the questions have some type of open ended text responses (2 lines at most for these). We have agreed to use Excel to compile and analyze the data so as many people as possible can work with the data. There are about 700 survey sheets to enter and analyze (each sheet has the 35 questions). The data entry will be done by about 10 different volunteers with various versions of MS Excel (both Windows & Mac). My task is to: 1) create an Excel template that I can email to the 10 data-input volunteers so they can keyboard the data from the survey sheets into the template. 2) Receive by email the 10 different Excel data files that the volunteers will create from my template. 3) compile/merge the 10 Excel files into one master file. 4) Be able to analyze and present the data from the 700 survey sheets upon the community's request. I was thinking that I would create a template that has the 35 questions and possible multiple choice responses on the first sheet of the Excel page and then try and duplicate that 1st page for each of the 700 surveys, but it doesn't seem like a very elegant way to handle this. Can you think of a better way to do it? Your help would be greatly appreciated. All of the participants have volunteered their time and effort. No one will benefit financially from this project. thanks, rgraham1 Forgot to lock the references for copying. Formula should actually look something like this: =COUNTIF($B1:$BS1,COLUMN(A:A)) SignatureRegards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- One approach, for the multiple choice questions at least, might be to simply have Column A reference the questions, and have each adjoining column reference an individual questionnaire. With 700 surveys split among 10 volunteers, that makes just 70 columns necessary for your template. With just 35 questions, you could start your tally formulas at the bottom. Something simple, such as counting the number of the multi-choice answer (1, 2, 3, ... etc.). =COUNTIF(B1:BS1,COLUMN(A:A)) Copy across 10 columns ( max number of choices), And down 35 rows (max number of questions) You'll have to exclude the text answers of course. Then just total the 10 templates. SignatureHTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== Hi, I'm looking for a little advice. I have volunteered to use Excel to analyze the results of a big bunch of surveys that some teenagers from the local school have created and answered. And I'm looking for your advice on how to go about it, before I get started down the wrong track. The survey is about 35 questions long. It's mostly multiple choice questions (~90%). There are between 2 and 10 multiple choice answers to pick from on these questions. About 10% of the questions have some type of open ended text responses (2 lines at most for these). We have agreed to use Excel to compile and analyze the data so as many people as possible can work with the data. There are about 700 survey sheets to enter and analyze (each sheet has the 35 questions). The data entry will be done by about 10 different volunteers with various versions of MS Excel (both Windows & Mac). My task is to: 1) create an Excel template that I can email to the 10 data-input volunteers so they can keyboard the data from the survey sheets into the template. 2) Receive by email the 10 different Excel data files that the volunteers will create from my template. 3) compile/merge the 10 Excel files into one master file. 4) Be able to analyze and present the data from the 700 survey sheets upon the community's request. I was thinking that I would create a template that has the 35 questions and possible multiple choice responses on the first sheet of the Excel page and then try and duplicate that 1st page for each of the 700 surveys, but it doesn't seem like a very elegant way to handle this. Can you think of a better way to do it? Your help would be greatly appreciated. All of the participants have volunteered their time and effort. No one will benefit financially from this project. thanks, rgraham1 RD, So, across the top I'd have the titles. And in column A: I would have each row reference a question for the first questionaire (A2 => question #1, A3 => question #2, ect)? And in Column B: I would have each row reference a question for the second questionaire (B2 => question #1, B3 => question #2, ect)? Is this correct? Ron YES If you wish, all of Row1 is title headers A1 to BS1 Column A is just the question number: A2 = question1 A3 = question2 A4 = question3 ... etc. If the survey is not anonymous, you could enter the respondents name and/or number in B1, and then *just* the answers down Column B. Next respondent (questionnaire) in Column C. Next respondent (questionnaire) in Column D. ... etc. Out to Column BS (70 columns) So, B2 to BS35 is filled with nothing but just numbers, the number of the multiple choice selection picked by the particular respondent to answer that question. As the formula is copied *across* the columns, it automatically increments itself to give you the total 1's, 2's, 3's, ... etc. that were used to answer that particular question. To perhaps make the totaling easier to understand, In A40 enter "Quest #" And in A41 enter "Q1" And copy down to A75 to end up with Q35 Fill B40 to K40 with the numbers 1 to 10 In B41 enter this formula: =COUNTIF($B2:$BS2,COLUMN(A:A)) Copy it across to K41 Select B41 to K41 and copy down to K75 You now have a complete tally of answers chosen for each question for each multiple choice. You'll see a lot of zeroes, since you said the possible choices ranged from 2 to 10, so the formula is looking for 10 possible numbers to count. SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > RD, > [quoted text clipped - 9 lines] > > Ron Ahhh, I should have provided more details initially on the survey... The questions can not be answered with a simple number response. There can be 1 or more answers per question. Many questions are a YES/NO response. However, there are a significant number of questions that can have up to 6 choices (eg. "check all that apply"). As well, we do have a few the the free text replies. I hope this doesn't throw a wrench into the engine. Ron What you *NOW* describe cannot be accomplished with just formulas. Post to the programming group, and see if someone will work up some code for you. Make sure that you *fully* describe *exactly* how the answers to the questions will be configured. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Ahhh, I should have provided more details initially on the survey... > [quoted text clipped - 7 lines] > > Ron RD, Thanks for your suggestions. I'm not willing to give up so fast though. I have a couple ideas. Can I use a different "Sheet" for each Survey (eg. Sheet1, Sheet2, Sheet3, ect)? A template like you suggested might be used for each Sheet. I would have the questions go down (question #1 goes in row #1, question#2 goes in row#2, ect). The answers would go in each column next to its question. Or a second alternative would be to have each question on its own Sheet. Then each row would be a new survey and each column would be the the answers to that sheet's question. If I go with the above can I create graphs that span across different Sheets? The last idea I had was to have the each Survey be in its own row and then each Question would occupy a group of 6 adjacent columns (Question #1 => B2, C2, D2, E2, F2, G2; Question #2 => B3, C3, D3, E3, F3, G3; ect) Might one of these ideas work? Ron I didn't say give up! Multiple answers to the same question. Some "Yes" and "No". Some with either a single response or 6 responses. You need code! The programming group has a bunch of folks who might jump on such a project and gladly offer suggestions. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > RD, > [quoted text clipped - 25 lines] > > Ron RD, When I hear Programming I think of it costing money or taking large amounts of time. If I am wrong, could you help me by pointing me in the right direction? Before I go, do you think any of the suggestions I made before would work? regardless - thanks for the help, Ron It'll cost you as much as you spent here ... nothing!<g> microsoft.public.excel.programming I think you got here through google. You can get there the same way. Same bunch of nice folks, all helping for the sake of helping. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > RD, > [quoted text clipped - 9 lines] > > Ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.