C STA1DCT Computer Laboratory 4 The main aim of this computer laboratory is to become comfortable with scatterplots and correlation. 1. To become familiar with scatterplots and correlation in Excel, we consider data from the Old Faithful geyser which consists of eruption duration and waiting times until the next eruption. Open the file called Old Faithful_data.xlsx which is located on LMS. (a) Create a scatterplot of the data in Excel by carrying out the following: . Select all the data in Column A and Column B. Ask for help if you do not know how to do this. . Click on the Insert tab (this is where you went to find the pie chart and column graphs last week) and locate, and then press, the button that looks like a picture of a scatterplot. . Select the simple scatterplot which appears in the top-left of the selections that have appeared. . A scatterplot has now appeared. However we can make some simple improvements. Click on the Design tab and then try selecting some different options for the scatterplot pictures to change how it looks. · We can now also include appropriate axis titles. While your plot is selected, press the + button near the top-right of the plot and then tick 'Axis Titles'. Try and edit the axis titles so that they are meaningful. . Finally, try editing the main title so that you now have something you are happy with. (b) Are you happy with your scatterplot? Feel free to experiment with some of the other Excel features to further customize your plot. (c) Look closely at your scatterplot and have a guess at what you think the correlation between the two variables will be. (d) Type the formula =CORREL (A2: A299, B2: B299) into an empty cell to calculate the actual correlation. What is it? Was your guess close? 2. In part of the previous question we used Excel to include the linear line of best fit. Consider any linear line on a scatterplot and consider the ith observation (xi, yi). Let di denote the vertical distance of the ith observation from the line. Then the line of best fit is the line for which the sum of all of the squared dis- tances (i.e. __ d2) is the smallest. In simpler terms, we want the line for which, overall, the points are closest. In this question we are going to practise changing the slope and intercept to try and find a line that fits the data the best. Open the file called Computer Lab 4 Question 2. xlsx which is located on LMS. In this file you will find some data in the first two columns, a scatterplot of this data that included a red linear line, and values for the slope m (initially 2 and stored in Cell D2), intercept c (initially 5 and stored in Cell E2) and the sum of the squared distances (initially approximately 8762 and stored in Cell F2). The red line