Week 7 - Excel 2016 Instructions for Regression and Correlation PDF

Preview

Summary

Week 7 - Computer lab session Final 2017 (Excel), Week 7 - Computer lab session Final 2017 (Excel)...

Description

300831 Quantitative Thinking Week 7–Computer Lab Session Creating a scatter diagram using Excel 2016 To create a scatter diagram in Excel 2016, select the cells that contain your data. In the Insert tab, click on the Scatter button and select the Scatter with only Markers chart from the menu (points only, no lines).

A scatter plot similar to the one below should appear in your spread sheet.

Page 1 of 12

You can edit “Chart Title” to something meaningful “Father’s vesus Son’s Height”. To label the horizontal axis, in the Chart Tools click the Design tab, then Add Chart Element, then Axis Titles, and Primary Horizontal. A text box will appear below the horizontal axis, which you can modify.

Page 2 of 12

Repeat the above instructions (except click Primary Vertical at last step) to add label to the vertical axis, again modifying the content of the textbox that appears along the vertical axis appropriately.

Father's versus Son's Height 200

Son's Height (cm)

195 190 185 180 175 170 165 160 0

50

100

150

200

250

Father's Height (cm)

If you wish to change the scaling of an axis (and you really should for the horizontal axis), click on any number on that axis and a Format Axis menu will appear on the right side of the Excel window. Click on the forth icon (bar graph icon) under Axis Options to get access minimum and maximum bounds. In the following picture we show what happens as we change the scaling on the horizontal axis.

Page 3 of 12

In this case rather than have minimum 0 and maximum 250 it makes more sense to have the minimum at least 150 (try that, but then you’ll see 160 is even better) and notice that the maximum automatically changes to 200 which is fine in this case. Your scatter diagram should look like the one below.

Father's versus Son's Height 200

Son's Height (cm)

195 190 185 180 175 170 165 160 160

165

170

175

180

185

190

195

200

Father's Height (cm)

To add the regression line to your scatter diagram, right click on a data point on the graph. A menu will appear, select Add Trendline.

Page 4 of 12

Once selected a trend line will be added as will the “Format Trendline” options on the right side of the window. Excel has automatically selected the “Linear” trend line which is correct in this case. You may want to also add the equation of the trend line by ticking the “Display Equation” box.

Your scatter diagram should now look similar to the one below.

Father's versus Son's Height 200

Son's Height (cm)

195

y = 0.8334x + 31.351

190 185 180 175 170 165 160 160

165

170

175

180

185

190

195

200

Father's Height (cm)

Determining the correlation coefficient, slope and the intercept using the Excel functions To determine the correlation coefficient, click on the cell where you would like the correlation coefficient to appear and click on the and Formula tab and then the Insert Function button on the formula bar. An Insert Function dialogue box should appear on your screen. Select the Statistical category and the CORREL function. Click OK.

Page 5 of 12

Complete the Function Arguments dialogue box as follows: in the Array 1 box enter the range of cells that contain the values of one of the variables; in the Array 2 box enter the range of cells that contain the values of the other variable. Click OK.

To determine the value of the intercept, click on the cell where you would like the intercept to appear and click the Formula tab and then the Insert Function button on the formula bar. An Insert Function dialogue box should appear on your screen. Select the Statistical category and the INTERCEPT function.

Page 6 of 12

Complete the Function Arguments dialogue box as follows: in the Known_y’s box enter the range of cells that contain the values of the response variable; in the Known_x’s box enter the range of cells that contain the values of the explanatory variable. Click OK.

To determine the value of the slope, click on the cell where you would like the intercept to appear and click the Formula tab and then the Insert Function button on the formula bar. An Insert Function dialogue box should appear on your screen. Select the Statistical category and the SLOPE function.

Complete the Function Arguments dialogue box as follows: in the Known_y’s box enter the range of cells that contain the values of the response variable; in the Known_x’s box enter the range of cells that contain the values of the explanatory variable. Click OK. Page 7 of 12

Your screen may look like this.

Page 8 of 12

A correlation coefficient of 0.89 (to 2 d.p.) indicates a strong positive correlation between the son’s height and the father’s height. The regression equation that could be used to predict the adult height of the son from the height of his father is:

son  31.35  0.83  father Intercept

Slope

Now try the tutorial questions 1 and 2 from this week’s tutorial, Tutorial Sheet 5, as you MUST BRING your

Excel outputs to your tutorial class this week.

A copy of these questions (questions 1 and 2) is given

below.

Question 1 Since crickets are cold-blooded, there is a good reason to believe that the outside temperature may have an affect on their behaviour. In the following you are to analyse the relationship between the outside temperature and the number of chirps made by a cricket in 15 seconds. The data for a random sample of 20 crickets at various temperatures are shown in the table below. Outside temperature (°C) 31 24 34 29 27 24 23 28 22 32 26 28 27 29 25 24 30 27 32 26

Number of chirps in 15 seconds 52 35 56 47 44 38 33 45 32 46 42 45 43 46 39 32 46 41 48 38

Page 9 of 12

a)

In Excel, construct a scatter diagram for the data. Show the outside temperature on the horizontal axis and the number of chirps on the vertical axis.

b)

Does the relationship between the outside temperature and the number of chirps in 15 seconds appear to be linear?

c)

What can you say about the number of chirps as the outside temperature increases?

d)

By inspecting the scatter diagram, determine whether the correlation between the outside temperature and the number of chirps is positive or negative.

e)

Use Excel to calculate the correlation coefficient between the two variables. Does the value of the correlation coefficient indicate a strong or weak correlation between the two variables?

f)

Add the regression line to your scatter diagram and use it to graphically predict the number of chirps in 15 seconds when the ground temperature is: i) ii)

25°C 30°C

g)

Would it be appropriate to use the regression line from part f) to predict the number of chirps in 15 seconds when the ground temperature is 10°C? Explain your answer.

h)

Use Excel functions to determine the slope and the vertical intercept of the regression line and write the equation of the regression line.

i)

Use the regression line from part h) to estimate the number of chirps in 15 seconds when the outside temperature is: i) ii)

25°C 30°C

and compare your answers to your answers to part f). j)

Interpret the slope of the regression line. That means, explain how does the number of chirps in 15 seconds change as the outside temperature increases by 1°C.

For information on how do crickets produce sounds go to the following website: http://insects.about.com/od/grasshoppersandcrickets/f/howcricketssing.htm

Page 10 of 12

Question 2 A random sample of 17 male gray tree frogs was taken and their body temperature and time between mating calls were measured. In the following you will analyse the relationship between the body temperature and the intervals between the mating calls.

Body temperature in °C 15 17 22 25 29 34 18 21 24 31 27 19 18 23 29 26 25

Time between mating calls in seconds 6 4.5 3.8 3 2.8 1 5 3.1 2.6 2.3 1.8 3.6 5.3 3.7 1.8 2.1 1.9

a)

Obtain a scatter diagram with the regression line for the data, showing the body temperature on the horizontal axis and the time between mating calls on the vertical axis.

b)

By inspecting the scatter diagram, describe the relationship between the body temperature and the time between mating calls for male gray tree frogs.

c)

Use Excel functions to calculate the correlation coefficient between the two variables. Does the value of the correlation coefficient support your findings in part b)?

d)

Obtain the intercept and slope of the regression line from Excel and use it to determine the equation of the regression line.

e)

Interpret the slope of the regression line.

f)

Use the regression equation from part d) to predict the time between mating calls for a frog with a body temperature of 27°C.

Page 11 of 12

g)

Residual (or error of prediction) is the difference between the observed value and the predicted value of the response variable. Calculate the residual for the observation where the body temperature is 17 °C and the time between mating calls is 4.5 seconds.

Note: The sign of the correlation coefficient between two variables always matches the sign of the slope of the regression line. If the slope is positive, the correlation coefficient is also positive. If the slope is negative, the correlation coefficient is also negative.

Page 12 of 12...