Rubbish recording and reduction: Part 2

1. Collect the following objects (or alternatively use the provided Trash Cards):
   
   cigarette butts
   plastic bags
   banana skin
   cardboard
   paper bag
   plastic bottle
   apple core
   glass bottle
   orange peel
   aluminium can
   
   
2. Divide students into groups of three or four and ask them to predict the order in which each object will decompose.
   Ask students to try to predict how long they think each item will take to degrade.
   
   
3. Show them the fact sheet from the Department of Environment and Conservation 'Litter: How long does it take to breakdown?'
   Ask students to comment on how close their predictions were and if they were surprised by any of these decomposition times.

   Students use of the actual objects or the pictures of the objects in order to predict the order in which they will decompose

   Students view the fact sheet from the Department of Environment and Conservation and compare their predictions to what is shown.

Explain to students that the learning intention of this lesson is to use Excel to analyse and interpret our data surrounding rubbish output. We are going to be using Excel and the data that we have entered earlier to allow us to figure out the answers these questions:

• If we assume that everyone in Victoria is disposing of a similar amount of rubbish as the students at our school, then how much rubbish is potentially being thrown out a day across two schools?
  
  
• How much across ten schools?
  
  
• (Extension) How much across all the schools in Victoria?
  
  
• How long it will take, collectively, for all of this rubbish to decompose?

We are going to be learning three main concepts in this lesson:

1. How to use Excel formulas to calculate basic addition and multiplication
   
   
2. How to have Excel reference another cell to multiply by
   
   
3. How to have Excel output the number of years it will take for all our rubbish to decompose

Students participate in a whole class discussion surrounding some of the key concepts that they will be exploring throughout this learning sequence.

Students respond to questions relating to the learning intentions for this lesson and develop a deeper understanding of what they are learning about and why.

1. Explain to the students: 
   'In order for us to use the information we have gathered to answer the questions we have just discussed, we are going to have to make use of some maths to calculate the answers that we want.
   Remember that we are trying to find out both how much rubbish two schools would output, and how long it would take for this rubbish to decompose.
   Can you think of an equation that we could use to find these answers?
   How would we figure out these answers using the information we currently have? (This might be worth putting on the board).
2. Have students Think, pair, share their responses. 
   Give them about one minute to quietly think about what they currently think on the topic.
   Then two minutes to share those thoughts with the person next to them.
   Then share the input from each group as a whole class.
3. After some discussion, at least one group should have reached the conclusion that we will need to make use of multiplication in order to calculate the answers that we are looking for.
   
   
   • Hopefully they will also have realised that they will need to multiply the amount of rubbish by the projected number of students, and in order to calculate the time of decomposition they will need to multiply the amount of rubbish by the length of time that the specific rubbish type takes to decompose.
   • If they have not reached these conclusions, you will need to guide them by providing prompting questions such as:
     If everyone in a football team scored 3 goals, and there were 22 players in total, how would you figure out what the total the team scored would be?
     How does this relate to the question we are trying to solve?
4. Once students have participated in this discussion and have come to an understanding about the need to make use of multiplication to solve these questions, you now need to explain how Excel can assist in this process. 
   

   Explain that Excel can be used as a big calculator to solve multiple problems at once. It does this through the use of 'formulas'.
   Formulas are mathematical equations that tell Excel what to work out.

5. Open a new Excel spreadsheet on an interactive whiteboard, projector or TV connected to a computer.
   
   
   1. Fill the first column with the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 running down (So A1 would have 1 in it, A2 would have 2 and so on).
   2. Then select the cell listed as B1 (this will be right next to the 1 you have just entered). Explain to the students that you enter a formula by starting with the '=' sign. This is because wherever you enter a formula you are telling the program to output, or calculate, the answer there.
   3. Enter the '=' sign in B1. Once you have done this explain that you want to multiply (times) all the data in the cell on the left by 5.
      Demonstrate that you can do this by clicking on the cell on the left and then typing '*5' after it and pressing enter. 
   4. Explain that in Excel '*' represents multiplication. Now, ask the students three questions:
   
   
   • What would they have to change in order to have the equation multiply the number by 10?
   • What would they have to change in order to have the number 2 be the number that is being multiplied by 5?
   • Can they think of any way that they could have two of the numbers in the column multiply each other?
   • 
6. Have the students discuss their answers to these questions with one another for two minutes and then discuss the responses as a whole class. The understanding that they need to reach is that:
   
   
   • the formula for the first questions should read =A1*10
   • the formula for the second question should be =A2*5
   • they would need to reference two different cells in order for two different numbers to multiply each other, so they should have something along the lines of =A1*A2
7. Provide the students with a challenge. They need to have three different columns that have the numbers 1–5 in each of them in any order. 
   
   
   • Tell them to have the fourth column multiply each of the other three columns together.
   • As an extra challenge, ask them if they are able to figure out a way that they can add the whole fourth column together.
   
   Give them ten minutes to complete these challenges (though if they have grasped the concepts quickly it should only take about 5 minutes).
8. Once everyone has had some time to attempt to complete the challenge, ask the students to share their formulas.
   
   • For the initial section of the challenge they should have in the cell 'D1' something along the lines of =A1*B1*C1, and this should continue down the rows. 
     So 'D2' should read =A2*B2*C2.
   • For the final section of the challenge, to add up the whole row, the most obvious solution that a student will have come up with will be =D1+D2+D3+D4+D5. 
   
   Extension: If your students are capable of learning a little more advanced functionality of Excel then you can teach them the 'SUM' function.
   The SUM function does exactly the same thing as using addition to calculate the total of a number of cells, but the way that you enter it into a formula is =SUM(D1:D5). The important thing to explain to the students is that the 'D1:D5' represents an array. This means that it includes every cell between D1 and D5, and therefore it is exactly the same as writing 'D1+D2+D3+D4+D5'.

Students consider the guiding prompt and Think, pair, share their responses to it.

Students participate in a discussion in order to deepen their understanding of the type of mathematical equations required to complete this problem.

Students develop an understanding of the term 'formula'.

Students watch the modelled demonstration of what a formula is and how to enter one into Excel. They learn about the importance of the '=' sign in this process.

Students consider and discuss how they could change the modelled formula to meet the prompt requirements. They share their ideas with the class.

Students attempt to complete the challenge of multiplying three columns together.

Students view possible solutions to the challenge and become familiar with how the coordinates in Excel correspond with the data that is entered into particular cells.

For a list of Key terms and Excel vocabulary download this PDF.

Once students have had the chance to become familiar with the basics of how Excel functions work and how to have Excel calculate answers for them, then it is time to get them using their previously entered data to answer questions for them.

1. Explain that they have the freedom to lay out their work however they want: they can have their answers display in whatever cell they feel makes the most sense. They just have to have the following answers:
   
   
   • How much of each rubbish type in total is disposed of by the school?
   • How long would it take for the total amount of each rubbish type to decompose?
   • If we assume that everyone in Victoria is disposing of a similar amount of rubbish as the students at our school, how much rubbish is potentially being thrown out each day across two schools?
   • How much across ten schools?
   • [Extension] How much across all the schools in Victoria?
   • How long it will take, collectively, for all of this rubbish to decompose?
2. Explain that they have 30 minutes to try to create these solutions and that they may work in pairs if they wish.
   
   
   • Remind them to provide headings for their answers. For example, if your answer for the total amount of plastic rubbish is in the cell 'F1' then you might want to write 'Total amount of plastic rubbish:' in the cell before it, which would be 'E1'. 
   • While they are working be sure to touch base with them to make sure they are not completely stuck. 
     Be careful not to simply give them the answer, but continually point them back to the more simple example that we covered together earlier, and explain that everything they need to know to complete the task is right there. 
   
   Extension:
3. If one or more groups finishes very quickly, having grasped the basics of excel easily, provide them with the link to Waste Atlas and ask them if they can figure out a way to calculate what percentage of each category of waste our school has produced. 
   
   
   • Can they use a formula to do this for them?
   • Do those percentages match anything found on the waste atlas?
4. Alternatively, have students use the data they collected earlier concerning the amount of students that brought no rubbish, two or fewer pieces of rubbish, or three or more in order to create a 'scoring' system. This could simply be 3 points for no rubbish, 2 points for two or fewer, and 1 point for three or more.
   
   
   • Their task is to use Excel to calculate the score for each grade based on what has been brought in.
   • If your school decides to make use of Nude Food as a follow on activity to this learning sequence then this Excel solution could be used to calculate class scores.

Students consider the questions that they need to answer in relation to the concepts they have just learnt and the data they have previously collected. They ask any questions that they feel they need clarification on before the task has been started.

Students work on attempting to find solutions to calculating the questions that have been provided.

Once students have finished working out their waste production and the length of time that it will take for their waste to decompose, ask them to print out their graphs along with the calculations  (Simply go to 'File' and then 'Print).

• These can then be mounted on coloured card and displayed.
• Allow students the opportunity to view and discuss each other’s work.

Students print out their graphs and calculations. They then mount these on coloured paper and display them. They then view each other's work and look for similarities and differences.

After the students have finished looking at each other's work, pose these questions for them to answer in their books (one sentence each):

1. Did I manage to complete the task? Was it easy or difficult and would I have done anything differently?
2. Was I surprised by any of the findings or data I collected? Will this impact the way I live in any way?

Students complete both reflection questions in their book.