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.

Analyzing fast food data with Excel

Being the parent of two young adults, I know first hand how teenagers grab fast food without much thought to nutrition or how what they are eating affects our landfills.

burgerWanting to create more awareness of their eating habits and to use some authentic data in Excel, I created a Google Form and asked my high school students to add nutritional data about some of their favorite food items, ie, item, restaurant, calories, fats, carbs, and amount of waste produced (wrappers, straws, containers, etc.)

Students searched for the nutritional content of their favorite foods on the Internet. Many of the students were surprised about the fat & calorie content of burgers, fries and smoothies.

After everyone entered their data, I exported the Google spreadsheet into Excel and each student opened their own copy. We practiced sorting the data alphabetically, by calories, etc and used the filter to analyze the data.

Students selected items from the class data to create their own spreadsheet of healthy vs. unhealthy lunches. The students wrote Excel formulas to calculate totals, averages and percents. They selected specific data to graph.  Many had to change the values on the x axis to make the graphs comparable. The visual representation of the graphs clearly showed the differentiation between the two meals. Several students focused on lunches that produced less plastic/paper waste instead of nutrition.

excel_graph
Sample Excel graph of unhealthy lunch from Fast Food project

Using data that is relevant and meaningful made all the difference in this assignment. As a technology teacher I wanted them to see the power of Excel for data comparison but also wanted the assignment to be interesting and make a difference.

I overheard one student comment about how she thought having smoothies was a healthy snack but now has realized the one she was ordering had the same calorie content of a burger.

Photo credit:  http://www.flickr.com/photos/51035609331@N01/12315877