FLO Assignment 1 – Correlation and Regression Functions of Real Data
For this assignment you will use Excel to calculate the correlation coefficient and regression functions of some data from the River Torrens. Log onto www.waterconnect.sa.gov.au and click Data Systems in the top right. Click Surface Water Data, then Surface Water Data again (second from the bottom this time). Now find site “A5040529” at the following coordinates:
Latitude -34.9135 34:54:48.6 S
Longitude 138.5486 138:32:55.0 E
Click on the site, then find the “Historic Data” tab. Scroll down to the bottom of the page and download the following files:
• WinZip CSV Data File – Flow Volume Recorded Daily Total for all record.
• WinZip CSV Data File – Rainfall Recorded Daily Total for all record.
You will notice that one file has significantly more data than the other. For this exercise, only use data which overlaps, beginning on the 20th of September 2016.
Draw a scatter plot of these two data sets. Describe briefly what you can tell from it. Now use the CORREL function or the correlation command in Data Analysis Tools in Excel to find the correlation coefficient between the two data sets. Is it what you expected? Why/why not?
Now we will try using the regression command in Data Analysis Tools of Excel. This time we will add air temperature in as a variable. To get this data, log onto and download maximum daily temperature values from Adelaide Airport.
Perform a regression analysis of the temperature and rainfall data on the flow data. What can you say about the statistical significance of the two predictor variables? Comment on p and r2 values, and give reasons why you think you got these results.