Water Pollution Data from the River Taff

You have been provided with some data from the Environment Agency. Your task is to manipulate the data and produce some data so that you can produce a report on conditions in the Taff.There are 120 readings taken from 1996 to 2006.

You can put your answers straight into a PowerPoint presentation that can be sent straight to your teacher. A template file is provided “Pupil Water Data Presentation”

The order as the Taff runs towards the sea is Tongwynlais → Llandaff →Blackweir.

Task1: Draw a graph that shows the trendsin the water temperature from 1996 to 2006 at Blackweir.

  1. Click onto sheet 3, Blackweir (3), using the tabs at the bottom
  2. Highlight the date column by clicking on the B of column B, hold down control and click on the D of column D.
  3. Click on the chart symbol (or go to Insert then Chart).
  4. Go through the steps to create an XY (scatter) graph with smooth lines, and place the chart as a new sheet.
  5. Click on your graph, hold down Ctrl, and press C.
  6. Open the PowerPoint presentation, hold down Ctrl, and press V.

Which was the hottest summer?

Which was the coldest winter?

Is there a trend over the ten years?

Task 2: Draw a graph that shows the trends in the water temperature from 1996 to 2006 at all three sites

  1. Your date and temperature columns should still be highlighted (if not re-highlight them using step b in Task 1) press the copy button.
  2. Right click on the Blackweir tab at the base of the chart and click on Insert…
  3. Choose Worksheet and press OK
  4. Click on the paste icon. Change the temperature label to Blackweir.
  5. Select the Tongywnlais tab at the bottom of the page and select the temperature column by clicking on the D of column D. Hit copy.
  6. Select Sheet 1 at the bottom and paste your information by clicking on Column C and then pressing Paste. Change the title to Tongywnlais.
  7. Select the Llandaff tab at the bottom of the page and select the temperature column by clicking on the D of column D. Hit copy.
  8. Select Sheet 1 at the bottom and paste your information by clicking on Column D and then pressing Paste. Change the title to Llandaff.
  9. Select the whole sheet by clicking on the grey box in the top left. Click on Chart and follow the steps to create an XY (scatter) graph with smooth lines, put the chart in a new sheet.
  10. Follow steps e and f from task 1 to put your graph into PowerPoint.

Is there a trend down the river?

Are there any anomalies?

Task 3: Do you think there is a correlation between the time and the temperature of the water at the Tongwynlais site? Write down your prediction. NB All predictions are valid

  1. Click onto the Tongywnlais tab at the bottom
  2. Highlight columns C and D, and then hit the chart button.
  3. Create an XY (scatter) graph with no lines.
  4. To create a trend line right click on one of the data points and choose add trend line.

Task 4: Do you think there is a correlation between the amount of nitrate in the water and the amount of oxygen at any site? Write down your prediction.

Now find out if there is a correlation.

  1. Highlight the columns for nitrate and oxygen
  2. Create an XY (scatter) graph with no lines
  3. Look for a correlation in the data (or add a trend line).

Can you come up with a theory or idea to back this up?

Do you think there will be there same trend at every site?

Test your idea by plotting a graph.

  1. Follow the same steps as above but on the other sites sheets.

Task 5: A company is suspected of dumping industrial waste in the river Taff. The waste includes arsenic and lead. Plot a graph and use it to find out the dates when the pollution took place.

  1. Select the Blackweir sheet.
  2. Select the columns for Date, Arsenic and Lead by holding down Ctrl and selecting the letters at the top of the columns. Hit copy.
  3. Create a new worksheet (by right clicking on one of the sheets at the bottom) and paste your data into it.Highlight your data.
  4. Excel cannot recognise less than symbols (<) so you need to hit the find button (or go to Edit, Find).
  5. Click the Replace tab, “Find what:” type <1, “Replace with:” 0. Hit Replace All.
  6. Now type “Find what:” type <2, “Replace with:” 0. Hit Replace All.
  7. Now create an XY (Scatter) graph with smooth lines for the data.
  8. Look for when Arsenic and Lead levels rise.

How could the Environment Agency track down the company responsible?

Why are we concerned with Arsenic levels in the water?

Task 6: Using the data, come up with a prediction of your own and test it. Can your results be explain scientifically or it the correlation a coincidence? (If you are not sure, you could look this up on the internet).E.g. Does it rain more in spring than the rest of the year – look at date versus flow at Blackweir.

Now email your file to your teacher.