. Body Mass Index

Body Mass Index (BMI) is a measure of the fat composition of a person’s body. While having enough body fat is important to a person’s health, having too much fat can cause serious health problems. BMI is calculated using a person’s weight and height. Ideally, BMI should fall between 18.5 and 24.9.

The table on the BMI worksheet contains the height and weight measurements for a sample of men. Each man’s BMI has also been calculated and is listed on table.

Complete the Summary Statistics table using the COUNT, AVERAGE, STDEV.S, and CI functions. Use the CORREL and FORECAST functions to examine the relationship between weight and height.

1.1

Use the COUNT function in cell H5 to calculate the number of men in the sample.

Use C4:C203 as the argument for your function.

1.2

Use the AVERAGE function in cell H6 to calculate the average weight of the men in the sample.

1.3

Use the STDEV.S function in cell H7 to calculate the sample standard deviation of the weight of the men in the sample.

1.4

Use the CONFIDENCE.NORM function in cell H8 to calculate the 95% confidence interval for the weight of the men.

Use 0.05 as the alpha in your function.

Reference the count you calculated in cell H5 in your formula.

1.5

Use the AVERAGE function in cell I6 to calculate the average height of the men in the sample.

1.6

Use the STDEV.S function in cell I7 to calculate the sample standard deviation of the height of the men in the sample.

1.7

Use the CONFIDENCE.NORM function in cell I8 to calculate the 95% confidence interval for the height of the men.

Use 0.05 as the alpha in your function.

Reference the count you calculated in cell H5 in your formula.

1.8

Use the AVERAGE function in cell J6 to calculate the average BMI of the men in the sample.

1.9

Use the STDEV.S function in cell J7 to calculate the sample standard deviation of the BMI of the men in the sample.

1.10

Use the CONFIDENCE.NORM function in cell J8 to calculate the 95% confidence interval for the BMI of the men.

Use 0.05 as the alpha in your function.

Reference the count you calculated in cell H5 in your formula.

1.11

Use the CORREL function in cell J11 to determine if there is a statistical relationship between the height and weight of the men in the sample.

1.12

Use the FORECAST.LINEAR function in cell J15 to predict Jim’s weight from his height.

Reference Jim’s height in cell J14 as the value for x.

Reference the appropriate columns from the data table as the values for the known x’s and known y’s.

1.13

In cells J18 and J19, use the information you calculated about the confidence interval for weight to determine the lower and upper bounds for the 95% confidence interval for your prediction of Jim’s weight in cell J15.

Be sure to reference the confidence interval calculation rather than hard coding (typing in the values of) the confidence interval into your calculation.

Baseball

There are 30 Major League Baseball (MLB) teams. The table on the Baseball worksheet lists the 2019 and 1990 payroll and win totals for each team (notice that four teams were added after the 1990 season). Some MLB fans complain because the league does little to regulate the amount of money teams pay for salaries. These fans argue that the teams that spend the most money will win the most games. This would put teams from small markets (teams that earn less revenue) at a disadvantage. Complete the tasks to analyze if small market teams are at a disadvantage.

2.1

In cell I4, use the COUNT function to calculate the number of MLB teams in 1990.

Reference range E4:E33 in your calculation.

2.2

In cell I6, use the COUNT function to calculate the number of MLB teams in 2019.

Reference range D4:D33 in your calculation.

2.3

In cell I8, use the AVERAGE function to calculate the average salary for the teams in 1990.

2.4

In cell I10, use the AVERAGE function to calculate the average salary for the teams in 2019.

2.5

In cell I12, use the STDEV.P function to calculate the standard deviation for wins in 1990.

2.6

In cell I14, use the STDEV.P function to calculate the standard deviation for wins in 2019.

2.7

Based on your standard deviation calculations, were teams more similar in terms of wins in 1990 or in 2019?

Recall that standard deviation is a measure of how similar items in a list are (large standard deviations imply less similarity).

Select either 1990 or 2019 from the drop-down list in cell I16.

2.8

In cell I18, use the CORREL function to determine if there is a relationship between the number of wins a team earned in 2019 and the and the size of the team’s salary.

2.9

In cell I21, use the CORREL function to determine if there is a relationship between the number of wins a team earned in 1990 and the size of the team’s salary.

2.10

Is the relationship between wins and salary stronger in 1990 or in 2019?

Select 1990 or 2019 using the drop-down list in cell I24.

2.11

In cell I27, use the FORECAST.LINEAR function to determine how many games a team could expect to win in 2019 if the team’s salary was $150,000,000.

Reference the salary amount in cell I28 and the appropriate ranges in the Major League Baseball Salaries and Wins by Team table as arguments for your function.

2.12

In cell I31, use the CONFIDENCE.NORM function to determine the 95% confidence interval for wins in 2019.

Use 0.05 as the alpha level in your analysis.

Reference the appropriate calculations for size and standard deviation as the arguments for your function.

2.13

In cell I33, calculate the upper limit for the 95% confidence interval for your win prediction for a team paying $150,000,000 in salaries in the year 2019.

Reference your prediction in cell I27 and the confidence interval calculation in cell I31 in your formula.

2.14

In cell I35, calculate the lower limit for the 95% confidence interval for your win prediction for a team paying $150,000,000 in salaries in the year 2019.

Reference your prediction in cell I27 and the confidence interval calculation in cell I31 in your formula.