Excel: Transporation Survey

Excel survey

In this assignment you will use live survey data to analyze how students travel to school each day.   Begin by taking the survey. Click here to take the survey.

After everyone in the class has taken the survey (ask them) then look at the survey results and begin building your spreadsheets.  Click here to see the results.

You will create two different worksheets in the same spreadsheet, one for each question.

Part 1

question 1: How do you get to school each day?   Enter the total number of responses for each category (car, carpool, bike, skateboard, walk, other) on the survey.  Then create a chart or graph in Excel that best describes the survey results.  The graph or chart that you use is your choice.  Considerations include how to best present data visually.

question 2: How far away do you live from school? Enter the total number of responses for each category (less than a mile, between one and two miles, etc) on the survey.  Then create a chart or graph in Excel that best describes the survey results.  The graph or chart that you use is your choice.  Considerations include how to best present data visually.

Save the spreadsheet as transportation.

Print out your work and hand it in at the end of Part 3.

Part 2

Complete part 1 of this assignment before moving on to part 2.

This part of the assignment involves using the Autosum and Average functions.  Here’s a tutorial to refresh your memory on using both tools.

1. Open the transportation file that you saved from Monday’s class.

2. Review the responses from the survey and update your totals for both spreadsheets if necessary.  Click here to see the results.

3.  Create a ROW title labeled TOTAL.

4. Using the AutoSum tool, total the number of responses that are on your both spreadsheets.

5. Create a ROW title labeled AVERAGE.

6. Average the two columns of data using the AVERAGE formula in Excel.

Part 3 – sorting and filtering

Video tutorial: sorting
Video tutorial: filtering

1. Sort both spreadsheets by the number of responses in order from highest to lowest.  Use the SORT function in Excel.  Use the above tutorial if you need help.

2. Now go to the spreadsheet that shows HOW students get to school each day.

3. Create a FILTER that shows responses for each type great than 10.  Use the above tutorial if you need help.

4. Switch to the spreadsheet that shows how far students live from school.  Show only responses with great than 15 people in that category.  Use the filter function.

5. Print both sorted and filtered spreadsheets and hand them in.