Assignment 11

You should type answers to the questions below (except where noted otherwise), as well as paste tables/graphs/charts, in one MS Word document, which has the name:
ma103Assignment10YOURNAME. Saving in MS Word should automatically give this file the proper extension of .doc or .docx. You should upload this file in Moodle prior to the deadline. Clearly label which Activity and problem you are answering. It might be helpful to put in a "Page Break" between the answers for different Activities, where appropriate.

When you are asked below to create a graph or a table in Excel, you may simply paste the graph or table into your Word document. You do NOT need to import the entire worksheet associated with it.

Note that the data used in the Activities is newer than the data provided in the textbook or the Excel instructions provided.

Monday, 26 October

A. Activity 14.2, Divisor Methods
In this activity, you will work with two divisor methods of apportionment: Adams' method and the Huntington-Hill method. With the help of Excel, you will determine how many representatives in the House of Representatives would correspond to each state if these methods were used.

Each method uses a modified divisor, that is, a divisor other than the standard divisor, to determin each state's modified quota. If the divisor D is used, then a state's modified quota is:

modified quota = state's population / D

Then depending on the method used, all modified quotas are rounded up or down. In Adams' method, they are all rounded up; in Jefferson's method, they are rounded down. In Webster's method each modified quota is rounded up or down to its nearest integer. The method currently used in the U.S. to apportion representatives is the Huntington-Hill method. The method uses the geometric mean to round off quotas.

Adams' Method
1. In this method modified quotas are rounded up; thus the modified divisor needs to be greater than the standard divisor. Why? Explain what would happen if the modified divisor were less than the standard divisor.

2. Open the file EA14.1 State Population.xls . Compute the total U.S. population in cell B53. Name this cell using the Name box above column A on the spreadsheet. (You could use the name total, or any other appropriate name). Also, in cell A53, type the text label, Total Population = . Enter the total population in your MS Word document.

3. Compute the standard divisor in cell B55 using the relationship:
standard divisor = total population / total number of seats
Remember that the total number of seats remains 435.

Record this number in your MS Word document. In cell A55, enter the text Standard divisor = .

4. In column C, enter each state's standard quota using the relationship:

standard quota = state's population / standard divisor

Use cell C53 to find the sum of all states' standard quotas and enter this sum in your MS Word document. Label column C by entering Standard Quota in cell C1.

5. In cell A56, enter the text Divisor = and in cell D56 enter a number greater than the standard divisor. (Pick any number greater than the standard divisor - you'll change it later to find the "best" one.) Be sure to enter this new divisor in your MS Word document.

6. Label column D by entering Modified Quota in D1. In cell D2, enter an appropriate formula and then drag it down to compute the modified quota for each state obtained by using the modified quota for each state obtained by using the modified divisor you entered in cell D56. (Make sure you enter a formula that will keep the location "D56" fixed when you drag the formula. Recall that you can do this by either naming cell D56 or by using $ signs.)

7. In each cell of column E, enter the integer number obtained by rounding up the modified quota. (To accomplish this enter =INT(D2) + 1 in cell E2 and drag to autofill the column.) Label the column Number of Seats Using Adams' Method.

8. In cell E53, enter the sum of the numbers in cells E2 through cell E51. Enter this sum in your MS Word document, and explain what this number means.

9. If the previous sum is exactly 435, you have succeeded in using Adams' method to apportion House seats. Explain why this is so.

10. If the sum is not 435, then you need to try other numbers for the modified divisor until 435 is reached. Note that when you change the number in cell D56 (the modified divisor), the numbers in column E, including the sum in E53, will also change automatically.

Using the table below, record the divisors you've tried and the total number of House seats resulting from each divisor. Indicate which one is the modified divisor that actually works. Include this table in your MS Word document.

11. List the four states that would get the largest number of seats if you used Adams' method and the number of seats each would get.

12. List the four states that would get the smallest number of seats if you used Adams' method and the number of seats each would get.

Huntington-Hill Method
In this method, each modified quota is rounded up or down according to the geometric mean. The geometric mean of two numbers x and y is the square root of the product of the two numbers. That is,

Geometric mean of x and y = square root of ( x * y )

13. Find the geometric mean of 27 and 40, and compare it with the mean average of the same two numbers.

14. In the Huntington-Hill method, the geometric mean of the integer part of the modified quota and the next integer is calculated. If the modified quota is greater than the geometric mean, then the modified quota is rounded up. Otherwise, it is rounded down. For example, if the modified quota is 12.742, the geometric mean of 12 and 13 is calculated square root of ( 12 * 13 ) = 12.489. Because the modified quota 12.742 is greater than 12.489 (the geometric mean of 12 and 13), the modified quota is rounded up to 13.

Suppose the modified quota is 7.49. Would the modified quota be rounded up or down in this method? Show your computations and explain your conclusion.

15. In cell F56, enter 660,000 (this will be the initial trial divisor). In column F of the spreadsheet, enter the new modified quota using the number in F56 as the divisor. Label the column New Modified Quotas and enter the value from cell F2 into your MS Word document.

16. Label column G by entering Geometric Mean in cell G1. Use the following Excel instructions to compute the geometric mean of the integer part of the modified quota and the next integer for each state.

Instructions to Use Excel to Compute the Geometric Mean
In cell G2, enter (note that you should just be able to copy and paste the formula below into the cell)

=SQRT( INT(F2) * (INT(F2)+1) )

This is the square root of the product obtained by multiplying the integer part of the number in cell F2 times the next largest integer. Then drag the formula down to compute the appropriate geometric mean for each state.

17. Enter the number you obtained in G2 into your MS Word document. Using a calculator, you should verify that the number in G2 is in fact the geometric mean used by the Huntington-Hill method to round off the modified quota in F2.

18. Label column H Number of Seats Using Huntington-Hill Method and tell Excel to round each modified quota up or down, according to the geometric mean, using the following instructions.

Instructions to Use Excel to Round Numbers According to the Geometric Mean by Using an "IF Statement"
In cell H2, enter (note that you should just be able to copy and paste the formula below into the cell)

=IF( F2 < G2 , INT(F2) , INT(F2)+1 )

This formula tells Excel to see if the number in F2 is less than the number in G2; if it is, enter the integer part of F2; otherwise, enter the integer part plus 1, which is the next largest integer. Then drag the formula down to obtain the number of seats for each of the 50 states.

19. Enter the number obtained in H2 into your MS Word document. Explain how it is consistent with your computation for #17.

20. In cell H53, enter the sum of the numbers now in H2 through H51. If this number is 435, you have apportioned the House seats using the Huntington-Hill method. If the number is not 435, change the modified divisor used (which is in cell F56), until the sum of apportioned seats is 435.

Record the divisors you've tried and the total number of House seats resulting from each divisor. Indicate which one is the modified divisor that actually works.

21. List the four states that have the largest number of seats and the number of seats each has.

22. List all the states that have just one seat.

23. Name the states that would prefer Adams' method and those that would prefer the Huntington-Hill method. Explain your answer.

24. Copy and Paste your final spreadsheet into your MS Word document. Remove all the columns except four: (1) the state, (2) it's population, (3) the number of seats from Adams' method, (4) the number of seats from the Huntington-Hill method.

Summary
In this activity, you used Excel to investigate two divisor methods of apportionment: Adams' Method and the Huntington-Hill method, which is the method currently used to apportion seats in the House of Representatives. Using each of these methods, you found the number of representatives that corresponds to each state. You learned how to use Excel to compute the geometric mean and to round numbers according to the geometric mean.

Wednesday, 28 October

B. Read Topic 16 in the textbook, and work the assigned Exploration problems as listed for each day on the course web-site. You do NOT need to turn in the Exploration problems, but they are useful in preparing for the written exam.

C. Activity 16.1, Visualizing Football Scores: Measures of Center and Spread
In this activity, you will examine some data from the National Football League and investigate several numerical measures of center and spread and a graphical method particularly useful for comparing data sets.

The following table gives the points scored by the Pittsburgh Steelers in their 16 regular season games during the 2007 season. (Source: http://www.nfl.com/teams ).

1. Without doing any computations, look at the "points scored" values and estimate the "center" of the data set. Why did you choose this value?

2. Enter the dates and points scored and the column titles, as they appear in the table, into columns A and B of an Excel worksheet.

3. In Excel, sort the data by "Points Scored," in ascending order. By inspecting the data (no formula yet), determine the median value of the data and enter it in your MS Word document.

4. Use the following Excel instructions to find the mean and median points scored by the Steelers in 2007, and enter these values into your MS Word document. Also, describe how these values compare with your estimate of the "center" of the data in #1.

Instructions to Use Excel to Find the Mean and Median of a List of Data
- In cell A19, type the word mean, and in cell A20 type the word median.
- In cell B19, instruct Excel to calculate the average (that is, mean) points scored by entering the command = average(B2 : B17)
- In cell B20, and calculate the median points scored by entering the command = median(B2 : B17)

5. Looking at the center of a distribution doesn't give the whole picture; you might also want to measure the spread of the data. One way to do this is with the range of the data; that is, maximum - minimum.

What is the range of the Steeler's scores above?

Note that range might be misleading if one, or both, of the extreme values are outliers.

6. To get a better sense of the spread, we'll consider quartiles. The first quartile, denoted Q₁, is the median of the lower (smaller) half of the data. The third quartile, denoted Q₃is the median of the upper half of the data values. These are part of the five-number summary of a data set:

(1) minimum
(2) Q₁
(3) median
(4) Q₃
(5) maximum

Use Excel to find Q₁and Q₃for the Steeler's points-scored data, by using the median command and the appropriate halves of the data for each. (Note that there might be easier ways to calculate the quartiles in Excel, but this approach helps enhance the understanding of what quartiles represent.) Enter the five-number summary in your MS Word document.

7. On a separate sheet of paper, which you do not need to turn in, sketch a boxplot for this data set. First, set up a horizontal axis. Next, mark a scale on your axis, then locate each of the five numbers in the five-number summary on the axis, and complete the boxplot.

8. The following table gives the points scored in the 2007 regular season games by the Philadelphia Eagles and the Cleveland Browns. Look at the "Points Scored" by each team and enter your estimate of the "center" of the data, for each, into your MS Word document.

The file EA16.1 Points Scored 2007.xls contains the above data. Copy and past this data into the Excel spreadsheet you've been using.

9. Calculate the five-number summary for points scored for both the Eagles and the Browns, and enter this into your MS Word document.

10. Based on your five-number summaries for each team, predict an outcome if these teams played against each other. Please give a thourough explanation. Then go to http://www.nfl.com/teams and determine whether these teams played in 2007, and what actually happened if/when they did play.

Summary
In this activity, you used Excel to calculate mean and median. You found the five-number summary for several data sets of football scores. You also drew boxplots and used them to compare the performance of different football teams.