MATH 22N-WEEK 8 ASSIGNMENT: PERFORMING LINEAR REGRESSIONS WITH TECHNOLOGY – EXCEL

Question 1

A personal trainer is analyizing their clients’ data and created the chart below.  The trainer recorded the heights (in inches) and weights (in points) of 30 clients.  This sample data is provided below. Use Excel to calculate the correlation coefficient r between the two data sets. Round your answer to two decimal places

Ans: The correlation coefficient can be calculated easily with Excel using the built-in CORREL function.

Open the accompanying data set in Excel.

In an open-cell, type “=CORREL(A2:A31,B2:B31)”, and then hit ENTER.
You could label the result of this cell by writing “Correlation coefficient” or “r” in an adjacent open cell.

 

The correlation coefficient, rounded to two decimal places, is r≈0.84.

 

Question 2

A personal trainer is analyzing their clients’ data and created the chart below.  The trainer recorded the heights, in inches (x) and weights, in points (y) of 30 clients.  Use Excel to find the best fit linear regression equation. Round the slope and intercept to two decimal places.

Answer below

To find the best fit for the given data, use Excel.

1. Open Excel. Enter the values for xin column A and yin column B. Highlight all the cells containing the data.
2. From the Insert tab, select Scatter. Use Scatter with only Markers, the first type of scatter chart. A simple plot is shown.
3. To add the linear fit to the graph, click anywhere inside the graph area. Select the Layout tab from the Chart Tools. Click on the Trendline icon and select the Linear Trendlineoption. The line of best fit is added to the graph.
4. For the equation of the line of best fit, click on Trendlineand select More Trendline Options. Check the Display Equation on the chart box. The equation of the line of best fit is shown on the graph.

Optional Instructions: Right-click on the Trendline and select Format Trendline. Select Linear on the right side of the screen and select  Display Equation on chart.

5. To change the number of decimal places in the trendline equation, right-click on the equation for the trendline and select Format Trendline Label….
6. Select Numberunder Category, change the number of decimal places to 2, and click Close. Thus, the equation of the line of best fit with slope and intercept rounded to two decimal places is yˆ=7.00x+(−292.02).

Question 3

A news reporter wants to assess the top 30 college quarterbacks.  The reporter recorded the number of plays (x) and the number of passes (y) a quarterback completed in one season.
Use Excel to find the best fit linear regression equation. Round the slope and intercept to two decimal places.

Question 4

A government lobby that supports government regulation of the snack industry is looking into snack foods. Looking at a sample of 20 unhealthy snacks (e.g., sweets) they recorded the typical portion size in ounces and the calories per ounce of each snack. This data has been included below. Use Excel to calculate the correlation coefficient r between the two data sets. Round your answer to two decimal places.

Question 5

The table below gives the average weight (in kilograms) of certain people ages 1–20. Use Excel to find the best fit linear regression equation, where age is the explanatory variable. Round the slope and intercept to two decimal places

Question 6

The table below contains the geographic latitudes, x, and average January temperatures, y, of 20 cities. Use Excel to find the best fit linear regression equation. Round the slope and intercept to two decimal places.

Question 7

A baseball scout wants to assess the talent at 5 local colleges.  The scout took the top 6 players at each school and recorded their slugging percentage (x) and average exit velocity (y) of a hit ball.

Round your answers to the nearest hundredth.

Question 8

The number of hours of homework spent per week, x, and the course grade, y, for students taking a math course are given below.  Use Excel to find the best fit linear regression equation.  Round the slope and y-intercept to three decimal places

Question 9

In the following table, the age (in years) of the respondents is given as the x value, and the earnings (in thousands of dollars) of the respondents are given as the y value. Use Excel to find the best fit linear regression equation in thousands of dollars. Round the slope and intercept to three decimal places.

Question 10

The table below represents the number of young people in a certain city enrolled in the academic support and enrichment program of youth services. Here, x represents the number of months from January 2011, and y represents the number of young people enrolled. Use Excel to find the best fit linear regression equation. Round the slope and intercept to two decimal places.

Question 11

A researcher is interested in whether the variation in the size of human beings is proportional throughout each part of the human. To partly answer this question they looked at the correlation between the foot length (in millimeters) and height (in centimeters) of 30 randomly selected adult males. The data is provided below. Use Excel to calculate the correlation coefficient r between the two data sets. Round your answer to two decimal places

Question 12

An economist is trying to understand whether there is a strong link between the CEO pay ratio and corporate revenue. The economist gathered data including the CEO pay ratio and corporate revenue for 30 companies for a particular year. The pay ratio data is reported by the companies and represents the ratio of CEO compensation to the median employee salary. The data are provided below. Use Excel to calculate the correlation coefficient r between the two data sets. Round your answer to two decimal places

Question 13

A baseball scout wants to assess the talent at 5 local colleges.  The scout took the top 6 players at each school and recorded their launch angle and batted barrel rate of a hit ball.  This sample data is provided below. Use Excel to calculate the correlation coefficient r between the two data sets. Round your answer to two decimal places.

 

Question 14

An economist is studying the link between the total value of a country’s exports and that country’s gross domestic product, or GDP. The economist recorded the GDP and Export value (in millions of $’s) for 30 nations for the same fiscal year. This sample data is provided below.

Question 15

An owner of a large car lot believes that fuel prices are going to rise significantly and wonders how this rise might affect demand for the high performance vehicles. Specifically, the owner is investigating a link between how fast a car can go from 0 to 60 miles per hour (measured in seconds) and the car’s economy as measured in miles traveled per gallon used (mpg). If fast cars, which are normally high in demand, are associated with higher mpg then there will be much less demand if gas prices rise as predicted. The owner gathers data on 20 vehicles. The data is provided below. Use Excel to calculate the correlation coefficient r between the two data sets. Round your answer to two decimal places.