Colette Cassinelli's visionary use of information literacy and educational technology

Make a self-calculating Survey

I am currently teaching Excel to my computer students and we always do a project where the students create surveys and graph the results.  I was inspired by Jesse Spevack’s self-graded quizzes – so I thought I would use the same process with surveys and teach some formula writing along the way.

surveyYou can download a PDF of these directions HERE.

Create form:

1.  Create a Google Form for the survey.  Only use “multiple choice” or “choose from a list” for the answer type because the answers must be consistent.  Consider making the last question required to prevent users from accidentally submitting their results before they are done.

2.  Double check spelling and directions for each question.  You don’t want to edit the survey once you begin.

3.  Email your survey (Google form) to the recipients or provide a link for easy access.  Google Forms can also be embedded in blogs, wikis and web pages.  See a live sample of this form at http://tiny.cc/NSrUf .

4.  Fill out a test sample on the form to make sure all questions work correctly.

5.  Answers to the survey are going to be automatically dropped into the corresponding Google spreadsheet.  Close the form and open the spreadsheet.  The spreadsheet indicates the time the survey was completed. Right click and rename Sheet1 to “Data”.  You do not need to wait until all the surveys are completed before going on to the next step.

Transfer the data:

6.  Add a NEW spreadsheet to your page by clicking the “Add Sheet” button on the lower left side of screen.  Rename the new sheet “Calculations”.copy1

7.  Copy the data from “Data” to “Calculations” using the following method.  Type this formula in cell A1 of “Calculations”:  =Data!A1 This will copy the information from the first spreadsheet (which we renamed “Data”) to the second one.

8.  Select A1 and grab the fill handle and copy this formula across to the other cells.  How far you copy the formula depends on how many survey questions you asked.copy2

9.  With all the cells in the first row selected, copy the formula all the way to the end of the page.  What we are doing here is transferring the data from the first spreadsheet to the second one.  We don’t want to do our calculations on the first sheet and interfere with the data collection.  Using the copy fill handle makes it easy to transfer the data. (Note:  we could use copy/paste if all the survey results were done, but doing it this way creates an automatic update for new results!).

10.  As new surveys are completed, the data will populate the “Data” spreadsheet as well as the “Calculations” spreadsheet.  Now we are ready to write formulas to calculate the results.

Calculate the results:

formula_results11.  Write SUMMARY in Row 1 of the column to the right of all the data.  This is where you are going to calculate the results.

12.  Write the question and answer options in this column.  Repeat down the column for all of your questions.

13.  Now we are going to write the formulas to calculate the results.  We will write the formulas in such a way that if more people fill out your survey, the results will automatically be updated.  In the column next to the first answer option write this formula:  =COUNTIF(C2:C100,H4)  with the range as C2:C100 (this is where the answers are located for the survey; the 2nd cell reference in the range needs to be large enough for all your survey results) and the criteria is H4 (this is the cell reference where the answer option is located).  It is much easier to use the cell reference than the actual answer.  Repeat this formula for each answer option and changing the criteria as needed, for example, the next formula would be =COUNTIF(C2:C100,H5) .

NOTE:  If you understand Absolute References, the formula could be written =COUNTIF($C$2:$C$100,H4)  so that you could easily copy the formula to the other answer options for that question.

14.  Now whenever someone completes the survey, the results will automatically be calculated.  You will have one spreadsheet with the raw data and the second spreadsheet with the data and results.

Optional: Create a graph of the summary results and watch the graph change as new results are calculated.



4 thoughts on “Make a self-calculating Survey”

  • Great post… one quick trick…
    In that COUNTIF formula – you can also use COUNTIF(C$2:C, H4) to make sure you include any number of rows which may come in as results. Not necessary with a quiz type form where you know the general number of expected responses, but helpful with open surveys to avoid missing later entries in your summary.

    Also – I assume you don’t want to tell people about the “Show Summary” option in the “Form” menu, since it does all the work for you in terms of counting responses for each multi-choice – which wouldn’t get people very far in learning formulas 😉

    Have fun!

  • @JR Yes, I usually have my MS & HS students create the COUNTIF formula to extend way past where they think the results will go (C2:C100) or more. Also, my students know about the Show Summary option but they are required to learn how to write formulas and make graphs so they usually just use that option to check their work.

  • Hi Colette
    I recently saw the results page from a google form reflected immediately as a graph and was wondering how it was done. Now I know!! Great instructions. Looking forward to trying it out 🙂
    Thanks.
    Patricia

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge