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

Annual Report II contest entry

Dan Meyer has done it again – another contest – Annual Report II

The basic idea is to DESIGN information in four ways to represent 2008 as you experienced it. If you look at my second graph (below) you’ll understand why I missed the first Annual Report contest – I was on a self-imposed “no computer” hiatus during January 2008.  But this year I used the opportunity to play around with Photoshop – something I never take the time to do.

These four slides best represent 2008 for me: School, Learning, Family and my PLN. 2008 has been a great year for me professionally.  I gave presentations at three conferences (ITSC, NCCE and ILC) and  I attended the Google Teacher Academy.  I blogged regularly and was active in many online learning communities. I joined the Oregon EdTech Cadre and met tech-loving teachers from all over the state.    I feel more comfortable in my role as a Computer Teacher than I ever have before and I think that is reflected in my teaching.

With that in mind, here are my four slides:

slide1

slide2

slide4

slide3

Slide 1: This bar graph shows the number of students who walked through my classroom door in 2008, starting first with the end of Fall /Winter Semester 1, Spring Semester 2 and Fall Semester of the next school year.  I teach 6 different classes each term.  There are not a lot of electives at my small high school and many students choose to take the advanced computer classes their Junior & Senior year.  I love having them all – but it does make for a busy day and lots of planning.  Image credit:  http://www.flickr.com/photos/cleopold73/2906486794

Slide 2: I started this blog in July of 2007 – right when I was finishing up my Master’s of Educational Technology from Pepperdine University.  I had just spent the last year and half blogging for the program and realized I enjoyed the connections I made and the reflective writing.  I never really considered myself much of a writer before but I enjoyed sharing what I was doing with my students in my classroom and resouces I discovered. I wrote 120 posts in 2008 – which I’m sure isn’t a lot for some people – but it is for me.  This 3-D cone graph was made in Excel.

Slide 3: This slide is a timeline of the major family events for 2008.  We traveled to Arizona for Spring Break to visit my husband’s parents and took a side trip to Sedona.  My youngest son graduated from high school and the summer was busy with 2 weddings and trips to the beach and lakes.  We traveled up  to Gonzaga University in the fall for Parent’s weekend and I attended the ILC conference in October – where I met the charming Mr. Meyer himself <smile>.

Slide 4: My Personal Learning Network exploded in 2008.  I have met and stayed in contact with so many fabulous teachers from the Google Teacher Academy, the Oregon EdTech Cadre and the conferences I have attended.  My favorite part of ILC was folks coming up to me and saying, “Hey, I follow you on Twitter – and then the next thing you know we are sitting next to each other, sharing ideas, commenting on each other’s blogs and entering fun contests!

Wow … I wonder what 2009 will bring?

I encourage you to participate.  Alice Mercer already has posted her four slides.  Come on – it’s fun!