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.

Instructional Technology Strategies Conference (ITSC) – February 17-19, 2008

Engaging Students with Authentic Technology Projects – Sun, 2/17/08, 7:30-8:30 pm

Handouts located at: http://edtechvision.wikispaces.com/ITSC

Embrace 21st century strategies to create an instructional learning environment that moves away from teaching isolated computer skills towards an integrated thematic approach. Come learn how you can combine everyday productivity tools with Web 2.0 to support curriculum integration and collaboration.


The National Technology Standards for Students (NETS*S) recommends that educators establish new learning environments that provide real world opportunities for students to utilize current information and technology resources (ISTE, 2000).

According to The American Youth Policy Forum (2000) instruction in today’s high schools must change. Disengagement from the learning process is a widespread problem in high schools as students do not perceive how lessons are relevant to their lives.

The American Youth Policy Forum reports that lecture style classes continue to dominate classroom instruction and content is divorced from the real world. Schools need to embrace new strategies for learning based on current research of how students learn, implement effective uses of technology in the classroom, and develop “21st Century Skills” while maintaining a rigorous academic curriculum (Burkhardt et al., 2003 http://www.ncrel.org/engauge).

It takes vision and planning to engage students in authentic learning experiences. Teachers must identify which instructional strategies are most effective and understand how to infuse technology into lessons with real world applications.


Instead of asking the question “What technology skills must students have to face the 21st century?” should we not be asking “What thinking and literacy skills must students have to face the 21st century?” These skills are not tied to any particular software or technology-type, but rather aim to provide students with the thinking skill and thus the opportunity to succeed no matter what their futures hold.” Justin Medved, Dennis Harter
Curriculum is designed from the best practice approaches to teaching and learning found in Understanding by Design

The Essential Questions for the 21st Century Learner are:

  • How do you know information is true?
  • How do you communicate effectively?
  • What does it mean to be a global citizen?
  • How do I learn best?
  • How can we be safe?

These questions speak to thinking, critically evaluating, analyzing, and communicating. They emphasize the value in responsible behavior and knowing yourself as a learner. http://newliteracy.wikispaces.com/


Career Unit – Based on John Holland’s six personality traits

Goals: critically evaluating, analyzing, communication

Work Samples:

 

Make a PhotoShow Full Size

Career Resources:


Lunch Nutrition and Recycling Unit

Goals: analyzing, communication, evaluating

Work Samples

Nutrition / Waste Recycling Resources


Other Classroom Activities:

TOP 100 TOOLS FOR LEARNING 2007 – http://c4lpt.co.uk/recommended/toolbox2007.html

  • Valiant Pride: New student edition – a newspaper that my students designed for new incoming students (samples:
  • Search Engine Extravaganza – PowerPoint presentations to teach our school community about unique search engines
  • BE SAFE ONLINE! Projects made by students to discuss online safety (including chat rooms, cyber bullying, online profiles, photo sharing, etc)
    • Wiki to store document sources used in projects
    • Samples of projects made on blogs, Flickr, and Google presentations (work in progress)
  • Curriculum Review Quizzes – Note: action buttons and hyperlinks do not work on slideshare – you must download the PowerPoint first

Note: Some of the lessons in this session are based on activities that I designed for my Action Research Project for my MA in Educational Technology from Pepperdine University. Three complete thematic units are located at http://cadres.pepperdine.edu/ar/c9/Cassinelli/arp.htm

 


Works Cited

  1. American Youth Policy Forum http://www.aypf.org/
  2. Best Jobs for the 21st Century, 4th Edition by Michael Farr. ©2006 JIST Publishing, Inc.
  3. Curriculum 2.0 http://newliteracy.wikispaces.com/http://newliteracy.wikispaces.com/http://newliteracy.wikispaces.com/
  4. enGauge 21st Century Skills: Literacy in the Digital Age (Burkhardt, G., Monsour M., Valdez, G., Gunn, C., Dawson, M., Lemke, C., et al., 2003) North Central Regional Educational Laboratory and the Metiri Group, http://www.ncrel.org/engauge
  5. National Educational Technology Standards for Students (NETS*S) http://cnets.iste.org/students/s_stands.htmlhttp://cnets.iste.org/students/s_stands.htmlhttp://cnets.iste.org/students/s_stands.html

Creating surveys using Google spreadsheets

Thanks for the tip from Darren Draper, I just learned forms.pngthat Google shared spreadsheets now has the ability to create forms to collect information and have the answers drop into a spreadsheet. How cool is that?

From Google: Create a form in a Google Docs spreadsheet and send it out to anyone with an email address. They won’t need to sign in, and they can respond directly from the email message or from an automatically generated web page. Creating the form is easy: start with a spreadsheet to get the form, or start by creating the form and you’ll get the spreadsheet automatically.

Responses are automatically added to your spreadsheet. You can even keep a closer eye on them by adding the Google Docs forms gadget to your iGoogle homepage, created by software engineers Valerie Blechar and Sarah Beth Eisinger (in her first month at Google!). It lists your recent active forms, with new responses highlighted. Add this gadget to your iGoogle page: Add to Google

I’m host a workshop next week at the ITSC conference in Portland and I was planning on sharing a project that my students did using Google shared spreadsheets and now I will have to discuss this too!! Wouldn’t it be great to have attendees each fill out the survey and then immediately show them the results on a shared spreadsheet???

Collaborating with Google Docs and Spreadsheets

Collaboration – (kuh-lab-uh-rey-shuhn) n. the act of collaborating. Working together for a common end.

Every class can benefit from enhanced collaboration among students and between students and instructors. Learning is social. Course assignments that encourage collaboration are effective way of encouraging interaction and discussion among students. Unfortunately, collaborative projects can also have its problems. With multiple students working on one project there can be issues with limited access to the materials or resources. While some of the learners are engaged in the project, others wait their turn or just don’t participate.

What’s for lunch?This past school year I incorporated Google Docs and Spreadsheets in my beginning Computer Applications course to facilitate communication and collaboration among my high school students. Google Docs and Spreadsheets is a free and relatively easy web-based tool for creating and collaboratively editing online documents in real time. It is well-suited for student collaborative projects because previous versions of the document are always accessible and it is easy to see who made which changes.

Our collaborative project was called “What’s For Lunch?” The goal of this project was to collect data about student’s eating habits at lunch and analyze the information for nutritional value, cost and how much waste was created. For one week, every student photographed a variety of student lunches and interviewed students about their food choices. The class compiled their photos together and began the task of analyzing the data. We decided to assemble data about nutrition (calories, fats, carbohydrates), cost of the meal, and we also created a point system to tabulate the amount of waste each lunch produced.

I set up a Google shared spreadsheet to gather the data. Each student created a Google account (which can be separate from a Gmail account). Although anyone could view the spreadsheet the students needed to be invited to edit and collaborate on the spreadsheet. Once the students accessed the file, they could begin to add their data to the spreadsheet all at the same time! Each user is color coded during collaboration and the document is automatically saved after each edit.

What’s for lunch2?I have never witnesses such enthusiasm and excitement for entering data into a spreadsheet before. Every student was engaged and involved in the documentation. Students could immediately see the edits being made other students which encouraged them to share nutritional information with each other. The whole process was motivating, easy and fun. From a teacher’s point of view, I could easily see a history of the revisions to determine who participated and how many entries they added. I also could add comments directly to the spreadsheet if I needed clarification of the data or more information.

After all of the data was collected, the spreadsheet was exported to an Excel spreadsheet where students wrote formulas to analyze and compare the data. Unfortunately, the data showed some poor eating habit by our student body. Another concerning element was how much garbage our students threw away from their lunches. Students graphed the results and created posters to try to influence the eating and recycling habits of their classmates. The posters included the lunch photos and data and were hung around the school.

During this same unit students used Google Docs to write letters to local state representatives concerning their opinion about a bill that would ban junk food in all public high schools. After writing their first draft, students invited collaborators and used peer editing to review the drafts and make corrections on their Google doc. We also set up a Google spreadsheet to collect the names and addresses of Oregon Senators and Representatives. The spreadsheet was exported to Excel and the addresses were adding to the letters using mail merge.

I continue to find many uses for Google Docs and Spreadsheets throughout my curriculum. Having the ability to work together on a document at the same times ensures that every student will be engaged during collaborative projects. Keeping the documents stored online provides access from both home and school and the automatic saving feature prevents losing work. The revision history allows documents to be restored to an earlier point and also makes students accountable for doing their part. Google Docs and Spreadsheets are just one of many exciting products from Google that you can use in your curriculum.

Resources:

Google For Educators: Using Google Docs and Spreadsheets http://www.google.com/educators/p_docs_spreadsheets.html)

Some ideas on how to use Google Docs and Spreadsheets in the classroom
Elementary school students collaborate to

  • read the same book and write a collaborative book report.
  • share their reflections of a field trip.
  • create a story from a story prompt
  • collect favorite poems for a class anthology
  • publish a weekly newsletter about class happenings
  • track profits from magazine sale

Middle school students collaborate to

  • write a science hypothesis about an experiment.
  • recreate an historical event.
  • develop a word math problem.
  • keep track of homework assignments
  • collect weather statistics

High school students collaborate to

  • create articles for the school newspaper
  • write a script for play
  • debate a current event
  • analyze data from US Census bureau
  • manage sales and profits from school store