Huntington Hill Method

rwd1981

New Member
Joined
Jan 22, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

I hope that I can find some help here. I would like to build a utility using Excel that will allocate representatives among several states using the Huntington Hill Method. I have already done so, in fact, but it is cumbersome and I would like scalability. I believe that both problems can be solved using the byrow and Lambda tools.

The most important part of the HH method is that it applies a recursive quotient to each state, evaluating the impact of adding an additional representative to that state's delegation. The state with the highest quotient gets that representative, which changes that state's total delegates, so that the calculation must be run all over again before the next representative can be assigned.

My current build of the utility has each state assigned a row and each representative assigned a column. The first columns, equal in number to the number of states, are given over to assigning each state a representative, since every state gets at least one. I then paste the quotient formula in all of the subsequent cells of the array. Each column has a cell at the top which finds the maximum quotient value among the states below it, and adds another representative to that state's total.

I don't like how many cells it takes up. I have 64 states and over 1,000 representatives. I also don't like how I will have to alter the array itself when I want to change the number of states or of representatives. I believe I should be able to create a list which expands or contracts to an input number of states -- that part I think I can handle. But I believe that I should also be able to set one cell as an input for the number of representatives total to be assigned, and another cell with a formula that uses byrow and Lambda to do all the heavy lifting. I think that formula can be made to 1) assign one representative to each state, 2) evaluate each state's need to be assigned an additional representative, 3) assign that representative to the state's total delegation, and 4) run that process again and again until the total number of delegates assigned between all the states matches the input.

Am I wrong? If it can be done, could someone help me with the syntax?

Thanks for reading. Thanks in advance for any help you can provide.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Based on my understanding of the Huntington-Hill method, I'm not sure you need recursion. You can do that, but since only one representative is assigned during each iteration, only one State's Constituency Priority Quotient (CPQ) will change during each iteration...the CPQ being the State's population or votes divided by the "Modified Divisor", D, where D is the geometric mean of the upper and lower representative quotas...with the lower quota representing the current allocation, n, and the upper quota representing the allocation should the State receive a representative, so n+1. Therefore D = sqrt(n * (n+1)). And the CPQ = (population or votes) / D. And since the decision regarding which State should be allocated the next representative is based on the maximum CPQ, you really only need to examine a sorted list of CPQ's after the initial allocation of representatives to determine the order of the States that will be allocated more representatives. All of this hinges on being able to make the initial allocation, which I believe is typically done by taking the total population (or votes) over all States and dividing by the number of representatives that are to be allocated over all States, thus establishing a baseline equi-representation ratio...some population (or votes) per representative that should approximately be applied to each State. Then each State's population (or votes) is divided by this equi-representation ratio and we take the FLOOR of that quotient (i.e., round down to an integer). The only exception is if the result of the FLOOR operation produces 0, we instead award 1 representative to that State since each State must have at least one rep.

All of this is done in columns A:F in this example, and by the time the calculations lead to column F, we know how many representatives' seats remain to be allocated (let's say that is y) and we can simply sort the CPQ's to determine the y-largest CPQ's and award each of them 1 more representative, at which point, all of the allocations are complete. The sorting and final allocations are shown in columns AB:AC.

To begin, I created a notional list of States and Populations:
Book1
AEAFAG
1Min Pop or Votes100,000
2Max Pop or Votes80,000,000
3
4
5Notional Data1311009370
6State IdxState NameState Population
71State158,522,682
82State215,114,666
93State366,845,002
104State454,756,201
115State513,850,711
126State653,928,483
137State760,537,641
148State829,265,417
159State935,142,314
1610State1030,775,540
1711State114,395,702
1812State1271,288,307
1913State1311,381,141
2014State1450,349,182
2115State1556,806,024
2216State1677096835
2317State1741639551
2418State1873688829
2519State1938656445
2620State2076790150
2721State2150691883
2822State227305114
2923State2326393599
3024State2453553618
3125State252091902
3226State2654507457
3327State2736267332
3428State2870904785
3529State2917162762
3630State3071300095
Sheet1
Cell Formulas
RangeFormula
AG5AG5=SUM(AG7#)
AE7:AE36AE7=SEQUENCE(C1)
AF7:AF36AF7="State"&AE7#
AG7:AG36AG7=RANDARRAY(C1,1,$AG$1,$AG$2,TRUE)
Dynamic array formulas.

And then these data are fed into the Allocation computation, where we see that after the initial allocation, 985 of 1000 representatives have already been allocated based in the equi-representation rules. Then based on the sorted order of CPQ's (see AB), we know that States in list positions 1, 4, 3, 11, etc. will receive representatives in that order until all allocations have been made. The green computation block is shown for the 1st allocation (after the initial Allocation 0). That computation block (all three columns) can be copied and pasted to extend the iterations, but that is not really necessary. I've shown two more computation blocks for information purposes, but we know after column F which States will receive more representatives, and we can jump over to columns AB:AC for the final result.
Book1
ABCDEFGHIJKLMAAABAC
1Number of States30
2Total Representatives to Assign1000
3Equal Representation ((pop or votes)/rep)1,311,009.37
4
51,311,009,370985Max Idx->1986Max Idx->4987Max Idx->39881000
6IdxStatesPopulation or VotesNum Reps: Allocation 0Modified Divisor, DConstituency Priority QuotientNum Reps Aft Alloc 1DCPQAllocDCPQAllocKnown After Allocation 0Final Rep Allocations
71State252,091,90211.4141,479,19822.449854,01522.449854,015212
82State114,395,70233.4641,268,93033.4641,268,93033.4641,268,930344
93State227,305,11455.4771,333,72555.4771,333,72555.4771,333,725636
104State1311,381,14188.4851,341,28088.4851,341,28099.4871,199,6789119
115State513,850,7111010.4881,320,6131010.4881,320,6131010.4881,320,613102411
126State215,114,6661111.4891,315,5631111.4891,315,5631111.4891,315,563111712
137State2917,162,7621313.4911,272,1891313.4911,272,1891313.4911,272,189131414
148State2326,393,5992020.4941,287,8762020.4941,287,8762020.4941,287,87620521
159State829,265,4172222.4941,301,0062222.4941,301,0062222.4941,301,006222023
1610State1030,775,5402323.4951,309,8942323.4951,309,8942323.4951,309,894231224
1711State935,142,3142626.4951,326,3612626.4951,326,3612626.4951,326,361263027
1812State2736,267,3322727.4951,319,0302727.4951,319,0302727.4951,319,030271628
1913State1938,656,4452929.4961,310,5762929.4961,310,5762929.4961,310,57629630
2014State1741,639,5513131.4961,322,0573131.4961,322,0573131.4961,322,057312232
2115State1450,349,1823838.4971,307,8813838.4971,307,8813838.4971,307,881381939
2216State2150,691,8833838.4971,316,7833838.4971,316,7833838.4971,316,783382938
2317State2453,553,6184040.4971,322,4124040.4971,322,4124040.4971,322,412401340
2418State653,928,4834141.4971,299,5764141.4971,299,5764141.4971,299,576411041
2519State2654,507,4574141.4971,313,5284141.4971,313,5284141.4971,313,528412741
2620State454,756,2014141.4971,319,5224141.4971,319,5224141.4971,319,522412641
2721State1556,806,0244343.4971,305,9724343.4971,305,9724343.4971,305,972431543
2822State158,522,6824444.4971,315,1994444.4971,315,1994444.4971,315,199442144
2923State760,537,6414646.4971,301,9604646.4971,301,9604646.4971,301,960462846
3024State366,845,0025050.4981,323,7285050.4981,323,7285050.4981,323,728502350
3125State2870,904,7855454.4981,301,0605454.4981,301,0605454.4981,301,060542554
3226State1271,288,3075454.4981,308,0975454.4981,308,0975454.4981,308,09754954
3327State3071,300,0955454.4981,308,3145454.4981,308,3145454.4981,308,314541854
3428State1873,688,8295656.4981,304,2785656.4981,304,2785656.4981,304,27856856
3529State2076,790,1505858.4981,312,7005858.4981,312,7005858.4981,312,70058758
3630State1677,096,8355858.4981,317,9435858.4981,317,9435858.4981,317,94358258
Sheet1
Cell Formulas
RangeFormula
C3C3=$C$5/$C$2
C5C5=SUM(INDEX(B7#,,2))
D5,AC5,M5,J5,G5D5=SUM(D7#)
F5,L5,I5F5=MATCH(MAX(F7#),F7#,0)
A7:A36A7=SEQUENCE(C1)
B7:C36B7=SORT(AF7#:AG7#,2)
D7:D36D7=IF(FLOOR(INDEX(B7#,,2)/$C$3,1)>1,FLOOR(INDEX(B7#,,2)/$C$3,1),1)
E7:E36,K7:K36,H7:H36E7=SQRT(D7#*(D7#+1))
F7:F36,L7:L36,I7:I36F7=INDEX($B7#,,2)/E7#
G7:G36,M7:M36,J7:J36G7=IF(F5=$A7#,D7#+1,D7#)
AB7:AB36AB7=INDEX(SORT(A7#:F7#,6,-1),,1)
AC7:AC36AC7=(SEQUENCE($C$1)<=$C$2-$D$5)+D7#
Dynamic array formulas.

If this approach is consistent with your allocation scheme, then it can be consolidated further and reduced to fewer columns of formulas.
 
Upvote 0
I think my understanding of the HH method was incorrect, so my previous post is probably not what you want. I assumed the initial allocation was based on applying the average population/representative to all states' populations, which results in a large number of initial allocations. That appears to be incorrect. Instead, every qualifying State is initially awarded one representative (I've assumed there are no exclusionary thresholds that would prevent a State from receiving at least the initial allocation of one). So if we begin with "S" States (in $C$1) and "T" Representatives to be allocated in total (in $C$2), a notional set of data are generated using random values for the populations of states (column O). To avoid issues with volatile random numbers affecting subsequent computations, I copied the list of random numbers and pasted them as values into an adjacent column (column N). Then to convert this range of population values into a spilling array, I multiplied the range by a SEQUENCE of 1's (in $L$8#). These populations are associated with States whose names are formed from another SEQUENCE of {1;2;3;...} (in $J$8# and $K$8#). The reason for doing this is to be able to bring the States and their corresponding populations over into the computation table in a sorted order with a simple spilling reference (in $B$8#). You should be able to replace this notional data in columns K:L with your actual data and revise the formula in $B$8# to specify the correct range for the actual data.

The initial allocations (round 0) result in n0=S allocations (in $D$8#), where n0 represents the sum of all allocations occurring during iteration 0, the initial seeding. This leaves the number of representatives to be allocated as T-n0=T-S (in $D$4). We construct a sequenced array whose maximum is the maximum number of iterations that could possibly be needed for the allocation of T-S representatives. This array is called N:
Excel Formula:
SEQUENCE(1,$D$4)
The modified divisors that apply to all States are unique to each iteration (or each element in N), and the array of divisors is called D:
Excel Formula:
SQRT(N*(N+1))
The Constituency Priority Quotient generated during each iteration is the State population divided by D for that iteration...so the array of CPQ values is:
Excel Formula:
INDEX($B$8#,,2)/D
Note that the state population cannot be referenced directly by the column where it is found because it is part of a two-column array, so INDEX($B$8#,,2) returns just the population column.
Finally, and similar to the point I made in my previous post, we know which States will be awarded representatives because those with the largest CPQ's are given priority, so to return the T-S largest CPQ's, we use the LARGE function with an internal SEQUENCE function that indicates whether the 1st largest, 2nd largest, etc. value in the CPQ array is to be returned. In order to convert this range of values into a spilling array, it is multiplied by a SEQUENCE of 1's.
Excel Formula:
SEQUENCE($D$4,1,1,0)*LARGE(CPQ,SEQUENCE($D$4,1))
All of these are assembled in a LET function to return just a single spilling array of the largest CPQ's associated with some State that is to be allocated a representative because of that CPQ (in $G$8#).
Excel Formula:
LET(N,SEQUENCE(1,$D$4),D,SQRT(N*(N+1)),CPQ,INDEX($B$8#,,2)/D,SEQUENCE($D$4,1,1,0)*LARGE(CPQ,SEQUENCE($D$4,1)))
Next, we need to find these largest CPQ values in the 2-dimensional CPQ array in order to determine which row (i.e., State) it is associated with. I've done this with a MATCH and BYROW function, where --(G8=CPQ) is the array passed to BYROW and the LAMBDA in that function simply sums any matches (so if there is a match on a given row, the sum will be 1, otherwise 0), and then feeding this array of 1's and 0's to the MATCH function and looking for where the "1" occurs, the MATCH function returns the row index...which corresponds to the State Index...the State Index is a helper column set up in $A$8#. I am not entirely happy with this approach, as this formula does not spill due the requirement to search the entire CPQ array for each element in G8# individually. There may be a better way to handle this. For now, the implication is that the row-finding step is not a spilling array, but a formula that needs to be pulled down beside the array of the largest CPQ's so that the formula can adjust to consider the CPQ to its left. This row-finding formula is:
Excel Formula:
MATCH(1,BYROW(--(G8=CPQ),LAMBDA(a,SUM(a))),0)
I've left this MATCH/BYROW construction inside a LET function that creates the CPQ array for convenience in case you or someone else devises a better way to determine which row in the CPQ array the column G values are found. Ideally, this would result in a spilling array. Alternatively, leave the formula as is and ensure that it is copied down far enough to cover the CPQ values...or copy it even further than necessary and ignore the errors.
Excel Formula:
=LET(N,SEQUENCE(1,$D$4),D,SQRT(N*(N+1)),CPQ,INDEX($B$8#,,2)/D,MATCH(1,BYROW(--(G8=CPQ),LAMBDA(a,SUM(a))),0))
I successfully tested this sheet with S=60 and T=1000 with no issues. Here is a small working example with S=6 and T=25:
MrExcel_20220522_Huntington-Hill.xlsx
ABCDEFGHIJKLMNO
1Number of States6Min Pop or Votes50,000
2Total Representatives to Assign25Max Pop or Votes100,000,000
3
4Remainder to Allocate19
5Reps Allocated625
6Notional Data254,198,148
7State IdxStatesPopulation or VotesNum Reps: Allocation 0Total Representatives CPQ's Known After Allocation 0 Add Rep to State with IdxInput Data IdxState NameState Population Values Only Random
81State 589,577,9711963,341,19111State 178,822,09178,822,09187,197,160
92State 178,822,0911755,735,63522State 240,241,36940,241,36974,315,188
103State 240,241,3691436,570,05413State 32,634,3332,634,33394,833,998
114State 432,428,5391332,178,98424State 432,428,53932,428,53946,942,250
125State 610,493,8451128,454,94535State 589,577,97189,577,97187,517,235
136State 32,634,3331125,858,93316State 610,493,84510,493,84585,340,256
14 22,930,440449,744,35295,603,104
15 22,753,978243,224,9932,550,264
16 20,030,24319,404,95550,259,142
17 17,625,155255,830,09413,470,636
18 16,428,470368,858,59596,782,215
19 16,354,625142,665,72665,069,508
20 14,390,87922,942,79553,260,538
21 13,822,181141,051,46757,259,518
22 13,238,896495,990,59654,167,782
23 12,162,513226,002,51963,787,825
24 11,970,360136,631,25555,790,582
25 11,616,6833653,46467,520,267
26 10,556,865122,692,00616,236,269
27 #N/A91,884,52927,997,841
HH_example
Cell Formulas
RangeFormula
D4D4=$C$2-$D$5
D5D5=SUM(D8#)
E5E5=SUM(OFFSET($E$8,,,$C$1,1))
L6L6=SUM(L8#)
A8:A13A8=SEQUENCE($C$1)
B8:C13B8=SORT(K8#:L8#,2,-1)
D8:D13D8=SEQUENCE($C$1,,1,0)
G8:G26G8=LET(N,SEQUENCE(1,$D$4),D,SQRT(N*(N+1)),CPQ,INDEX($B$8#,,2)/D,SEQUENCE($D$4,1,1,0)*LARGE(CPQ,SEQUENCE($D$4,1)))
J8:J13J8=SEQUENCE(C1)
K8:K13K8="State "&J8#
L8:L13L8=SEQUENCE($C$1,,1,0)*OFFSET($N$8,,,$C$1,1)
E8:E27E8=IF(A8<>"",SUM(D8,COUNTIF(OFFSET($H$8,,,$D$4,1),A8)),"")
H8:H27H8=LET(N,SEQUENCE(1,$D$4),D,SQRT(N*(N+1)),CPQ,INDEX($B$8#,,2)/D,MATCH(1,BYROW(--(G8=CPQ),LAMBDA(a,SUM(a))),0))
O8:O27O8=RANDBETWEEN($L$1,$L$2)
Dynamic array formulas.
 
Upvote 1

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top