Cs.appstate.edu



The Research Experience for Teachers Program Title: Apportionment using IF Functions and Goal Seek in EXCELMaterials List: MS Excel, Apportionment Spreadsheet Template (included in the zip file)Procedure:Background: Apportionment Methods are used to fairly divide representative seats proportionally according to population. For example, U.S. House seats are allotted to each state based on their population. Five methods, Hamilton, Jefferson, Adams, Webster, and Hill-Huntington have been used since the founding of the nation. Several of these methods depend on a modified divisor that can be found by trial and error. By correctly utilizing the IF argument and Goal Seek functions in EXCEL this process can be done with much greater efficiency.Preparation: Have the Apportionment Spreadsheet Template ready for studentsApportionment Methods Quick Guide: Standard Divisor = Population/Number of SeatsStandard Quota = Group Population/Standard DivisorRounding: Hamilton’s & Jefferson’s – Lower QuotaAdam’s Method – Upper QuotaWebster’s Method – Standard RoundingHill-Huntington Method- Upper Quota if > Geometric Mean Lower Quota if < Geometric MeanFinal Apportionment: Hamilton Method – Surplus seats given by largest fractional partAll other methods use a modified divisor if the rounded quota does not correctly apportion the seats Understanding the IF function: The IF function works on Boolean principles – there are only 2 possible answers. When you use the IF function you test a situation. If the test is true one thing happens. If the test is false, another thing happens. For example, every state or group is required to have at least 1 representative. This could be tested using the following if statement:=IF(E5=0,1,E5)This would examine if the value in cell E5 is zero. If it is zero, it replaces it with a “1”, otherwise it will remain the value in E5.Understanding Goal Seek: The Goal Seek function helps find a target value that gives a desired result. EXCEL will run up to 100 iterations to find this ideal value. This is extremely useful in apportionment to find the dreaded modified divisor. Goal Seek can be set to find the target amount of seats by changing the divisor until the result matches the target.The Research Experience for Teachers Program Activity:Open the Apportionment Spreadsheet Template provided by the instructor.Notice that the spreadsheet has 5 sheet tabs, one for each method.The Hamilton Method is the first tab. Since the Hamilton Method does not use a modified divisor, this sheet is inserted as an example.Formulas can be used throughout Excel spreadsheets to calculate efficiently, click on the indicated cells, looking at the entry bar to read the formula, and describe what the formula is calculating. *Note a cell name “A5” that has a $ inserted “A$5” indicates a fixed cell.Click CellRecord the Formula Explain what Excel is calculatingC11I7D6E6F6Click the tab for Jefferson’s Method. Now you will enter the formulas to perform Jefferson’s Method. Complete the spreadsheet by entering the appropriate formulas in key cells. The formulas for C11, I7, D6, E6, and F6 will be the same as the Hamilton Method. Drag down from the right corner to fill down the columns. Then check to see that they are working correctly. Click cell E11 and write a formula to find the sum of the Rounded Quota. Click cell F11 and write a formula to find the sum of the Final Apportionment. Record Below:Click CellRecord the Formula Explain what Excel is calculatingE11F11 In Cell I9 type in the Standard Divisor from Cell I7 rounding to two decimal places. This will be your initial modified divisor.You will need to use Goal Seek which in the Data tab under what if analysis to find the modified divisor. 405574548895 You will need to Set Cell F11, the Total Apportionment to reach 36 seats by changing Cell I9, the modified divisor. Make sure the modified divisor is recorded in Cell I9 and the final apportionment in column F.Click the tab for Webster’s Method. Fill in the formulas that should be the same as Jefferson’s method.Webster’s method is different because the quota is rounded using standard rounding rules. Using Excel formulas, help, or Google, find the correct formula for rounding and use it in Cell E6. Record the formula here: Complete the Apportionment using Goal Seek again to find the modified divisor and complete the sheet.Click the tab for Adam’s Method. Fill in the formulas that are the same. Adam’s method rounds up. In Excel you can use “Ceiling” to round up. Find the appropriate formula and use it is Cell E6. Record the formula here: Click the tab for Hill-Huntington Method. Fill in the formulas that are the same.Hill-Huntington Method rounds based on the Geometric Mean. Enter this formula “=SQRT(CEILING(D6,1)*FLOOR(D6,1))” to find the Geometric Mean in Cell E6. Explain the formula: __________________________________________________________In the Hill-Huntington Method apportionment seats are rounded up in the quota is greater than the geometric mean and rounded down if the quota is less than the geometric mean.You will need to use an IF statement with a Boolean (>, <. or =) to test which way to round. For example, =IF(E5>E6, round up, round down) In Excel up is “Ceiling” and down is “Floor”. Look back at the formula for step 17 to complete this IF formula. Try your formula in the Excel sheet to see if it works correctly.Finish the Apportionment using Goal Seek to find a modified divisor if necessary and complete the spreadsheet.Results/ConclusionsMake sure each method is completed correctly on the spreadsheetSave the completed sheet, then make a copy to modifyChange the number of Seats to 41Complete the new sheet to find the Apportionment for 41 seatsDo the methods result in different apportionments? How many?Can you find a number of seats that gives you more than 3 different apportionments?Submit your finished Spreadsheets and this lab sheet as directed. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download