How to Model Congressional Apportionment in Google Sheets (Or Excel!)
Build your own model to calculate how many congressional districts each state gets based on it’s population.
Congressional apportionment is the process that determines how many seats each state gets in Congress. The current apportionment method has been in place since 1941 and is called the “Method of Equal Proportions”. The Census Bureau has a handy explanation for how it’s calculated (Spend some time reading and understanding how the method works, this explanation will focus on how to model it).
Here’s the congressional apportionment model in Google Sheets. Follow along there.
Sheet 1 - Build your multiplier list
Multipliers are descending, long, non-integer numbers between 0 and 1. We’re going to multiply the population of each state by each multiplier to generate unique priority values.
List as many multipliers as California could possibly need (At least 60). Since the multipliers are always the same, you can copy them from the Census Bureau or build them over a few columns using the formula 1/sqrt(n*(n-1)).
Sheet 2 - Build an input for state populations
I listed states in alphabetical order horizontally, which will make it easier to calculate the priority values table. You can copy in vertical state population lists using Paste Special - Transpose.
Sheet 3 - Generate 60 priority values for every state
Here’s where things get exciting! List each state alphabetically across the top in row A, and each new congressional district value in column A (Remember, each state automatically starts with one district. If apportionment were purely based on population seven states would get no representation in the House).
In B2, the first priority value for Alabama, paste:
=’State Population Input’!$B$2*Multipliers!$D2
This multiplies the population of Alabama by the the first number in our list of multipliers. The corresponding number is Alabama’s first priority value.
Fill right across row 2 to generate the first priority value for every state.
Grab the whole row from B2:AY2 and fill down to row 60. This will generate 59 priority values per state.
Sheet 4 - List the largest priority values and figure out which states they belong to
We’re ready to start handing out the 385 remaining congressional districts!
Create a placeholder column 1–385, and use the “Large” function to pull out the largest priority values in descending order.
=LARGE(‘Priority Values’!$B$2:$AY$60,A2 )
Now assign which state each priority value belongs to. The number of seats a state gets is how many time it appears on the list until Rank # 385.
=INDEX( ‘Priority Values’!$B$1:$AY$1,SUMPRODUCT(MAX((‘Priority Values’!$B$2:$AY$60=C2)*(COLUMN(‘Priority Values’!$B$2:$AY$60))))-COLUMN(‘Priority Values’!$B$1)+1)
Use SUMPRODUCT instead of VLOOKUP or INDEX/MATCH because we’re working with an array. The function is a pain but returns the first row of the column where the priority value is found, which is the state.
Sheet 5 - Count how many times each state got a district
Use a COUNTIF function to see how many times each state appeared in the list, using the state as the search key.
=countif(‘Priority Value List’!$D$2:$D$386, A2)+1
Don’t forget to add the “+1” for the first seat each state gets.
Look at you! You built a model of congressional district apportionment in Google Sheets! (Or Excel!)