Maths Fun - Exploring Real Data with Excel

This term my Year 3 class is learning to collect, organise and represent data. I feel strongly about giving students opportunities to work in real world contexts and using available tools, so try to plan Maths lessons to achieve this. Early in the term students conducted mini surveys to find out about class preferences, recording the results in tally form. They then input their results using Excel and created graphs to show what they found.

This week we worked as a class on a project using the school lost property basket. We wanted to sort through what was there and find the total value of the items in lost property. On arrival at the basket, we found additional ways to sort the items. Within the basket were lunch boxes and containers, non-school uniform clothing items, and school uniform clothing items. For the sake of our task, we decided to work only with the clothing. We carried it all to an outside space where we could sort it.

Uniform items were set out in a physical graph.

Students sorted through the clothing, initially separating the uniform from non-uniform items. Uniform items were then sorted according to type and set out to form a physical graph. We put sticky labels on items that were named, so that we could also record this data, and possibly return the items at the end of our task.

Non-uniform items were separated.

One student was responsible for being our photographer and another wrote down our findings in tally form. We counted the number of non-school uniform items, but didn't sort these, as it would be harder to determine the value of these.

On return to class we used Excel to organise our results. To begin with, we wrote out all the items and the total number we found of each item. I introduced the formula aspect of Excel, demonstrating how to calculate the total number of items with =SUM(B1:B14). We then used the uniform price list to find the cost of each item, and entered it into the spreadsheet. We used another formula =PRODUCT(B2:C2) to find the total cost for each item in column D. I demonstrated how to fill down for the rest of the items. Finally we filled right from B15 to get the total cost. Students were blown away by the total value of the lost property. It was much more than they had expected.

Working more with the data we had collected, we made a table to show what we found about labelling. We copied and pasted the first two columns from our previous table, and then added a column for the number of labeled items. We tried to make a graph with this information, but it wasn't representing what we wanted to show, so we had to consider other options. We then tried to find a formula for subtracting, and eventually discovered that we just needed to type =(B2-C2) in D2 and then fill down in order to get a column for Unlabelled. We were then able to hide column B in order to graph the results to show the total amount of each item with labeled and unlabelled displayed.

We then made one more quick graph to show the comparison of Uniform to Non-Uniform items.

We sent our findings to the Head of School and Junior School Director, as something that might be mentioned at the SRC meeting as a reminder for students to write their name on their clothing, check the lost property and take greater responsibility for their property.

The task took about 90 minutes from start to finish, and involved a lot of modelling in the classroom rather than students having a chance to work it all through for themselves on the computers. Many are now keen to have a go with Excel to create their own spreadsheets and use formulas. My plan is to try this next week, with students calculating the value of a simple summer wardrobe.