Is Your State Over- or Under-Represented in the House?

DISCLAIMER

The Excel VBA for Apportionment (EVA) system is available as a free download. EVA is provided “as is” with no guarantee that it will perform as claimed. Additionally, you run the software at your own risk. You agree that I will bear no liability for any damage it may cause. These and many other details are covered in the End-User License Agreement. Click here to download a copy. 

If you want a pdf version of this article, there’s one way down past the endnotes.

Introduction

The question I want to answer is in the title: Is your state over- or under-represented in the House?

This is the first of quite a few posts about expanding the size of the U.S. House of Representatives. Advocates argue that, with 435 seats, each representative is asked to speak for too many people. Using 2020 Census data, each member of the House represents 761,169 residents on average. If we wanted 75,000 residents per House seat, there would be 4,414.78 representatives. That number is tantalizingly close to 4,350 (ten times 435). Many of my examples use 4,350, with each representative covering 76,117 people. (The Excel workbook “Excel VBA for Apportionment Misc Calculations” has a worksheet that does these calculations. This workbook and three others are included in this zip file.)

I want to look at the mechanics (how seats get apportioned among the 50 states), the implications for representation, and the impact on the budget. Currently my system can handle the apportionment process. Work in progress will add calculations of how the cost of running the House of Representatives is likely to change. Total cost is not a linear function of the number of representatives because of varying travel expenses. Also, the amounts allocated for field offices vary with each state’s cost of living.

The purpose of this article is to give a quick example of what can be done with this system. The EVA handles most of the calculations. After that, you can use the results any way you want.

An Example

As you probably know, the U.S. Constitution mandates that each state have at least one representative in the House. That leads to somewhat unequal representation. After the 2020 reapportionment, Delaware (1 representative) has 990,837 people per seat. Montana (2 representatives) has 542,703.5 people per seat. Even with 435 seats, “one person, one vote” still doesn’t apply precisely.

Suppose instead there were 4,350 seats, 10 times as many as the current seat count. If there was equal representation per seat, each state would have exactly ten times as many representatives. In fact, that is not true. (Interestingly, California has 52 seats and would have 520 with 4,350 representatives. That is the exception rather than the rule. There are four states equitably represented. Alaska, Arkansas, and North Dakota are the other three.)

Using EVA we can calculate the representation for each state with 4,350 seats. We can then compare those totals with ten times the 435-seat representation. For example, Alabama has 7 seats in the current House. With a 4,350-seat house, the state would have 66 seats, 4 fewer than ten times 7. One way of looking at this is that Alabama has 4 more seats than it would have if seats were assigned more proportionately. (The 4,350-seat version allocates the first 50 seats to each of the 50 states. But that has far less impact with the larger number of seats.)

Let’s describe this as Alabama currently having 4 seats excess representation. At the other extreme is New York. Today the Empire State has 26 seats. With 4,350 representatives, NY would have 266 seats. New York is underrepresented by 6 seats. Ohio is next with 5 fewer seats than it would have with 4,350 total.

Table 1 shows the complete results. The rightmost column shows the number of seats over or under representation for each state.

Table 1: Over and Under Representation

Montana and Rhode Island are overrepresented by six seats. New York is underrepresented by six seats, with Ohio having five too few.

Figure 1 Is a graph of the above data.

Figure 1: Over- and Under-Represented States. Click for larger image.

This is one example of what can currently be done with this system.

The Census Process

The Census process consists of 5 steps:

1. Calculate a Priority Value for each apportioned seat for each state. Each seat will have 50 priority values, one for each state. However, the first 50 seats are already apportioned because the Constitution requires each state to have at least one seat in the House. Thus, with 435 seats, there are 385 to be assigned via the apportionment process.
2. Paste all the priority value tables into a single worksheet. For 385 apportioned seats the table will have 50 x 385 = 19,250 rows. Add one more row if you use a header row.
3. Sort the table by Priority Value from largest to smallest.
4. Assign 385 seats using the first 385 rows in the table. It’s helpful to create a column containing the numbers from 1 to 385 to keep track of which seat you’re working on. Also, keep track of the number of seats assigned to each state.
5. When all seats are assigned, add one to each state’s apportionment. You should have assigned 435 seats.

Priority Values obviously have a very large role in this process. The Priority Value for a state is the multiplier for that seat allocation times the state’s official population (from the decennial census).

Well, THAT didn’t explain very much. What’s this “multiplier?”

The multiplier for each seat to be allocated is the inverse of the geometric mean for the seat number being allocated.

The first seat is 2, so the multiplier is

For n = 3

Reminder: seat number 2 is the second seat to be allocated among the states. There is no seat number 1 because each state automatically gets one seat.^[1]

The calculated multiplier then is multiplied by the state’s population to get that state’s Priority Value for that seat.^[2]

All 50 rows for each seat are pasted into a single worksheet, then sorted from largest to smallest Priority Value. In all cases, the first state to get a second seat is California (the state with the largest population). That’s seat 51. Texas gets seat 52 (seat number 2 for that state). California gets 53 (seat number 3 for that state). Table 1 shows the apportionment for seats 51 through 68.

For those wondering what priority values look like and how my results compare to Census, here’s a blended worksheet. This is the sheet Priority Values from Census. Columns A through D are Census data. Column E is my calculated Priority Value. Column F is my Priority Value minus Census’ Priority Value. The first priority value for which my value is different from census is row 116 (seat 162). The difference is +0.0000001. I can live with that.

Census and Calculated Priority Values. Click for larger image.

My Process

I used Excel Visual Basic (VB) and Excel workbook functions to do calculations, formatting, and assign the seats. The workbooks where most of the work gets done have the extension .xlsm (instead of the usual .xlsx). That means they are “macro-enabled” workbooks. Microsoft introduced this format starting with Office 2007 (if memory serves). The purpose was to limit the danger posed by macros which can be easily hacked. My advice: disconnect your computer from the internet (both Ethernet and wifi) while you’re working with this software. I will add that I have spent many hours working with these files and never experienced any difficulty.

The process involves five steps, incorporated into a single Visual Basic script. Here are descriptions of each of the VB steps.

1. For each seat number, calculate priority values for each state. Those values (for sequential seats) are stored on the Working sheet. (segment AAutoParseItems). That will be seat number 2 for one of the states (most likely California). The Working worksheet is initially a copy of the TEMPLATE master sheet. The seat number is entered by the software. Excel calculates the Priority Values for that seat automatically.
2. Copy data from Working to the sheet Priority Values Calculated (segment BCopyPasteDataValues). The data is concatenated. Thus, the data for seat 2 is copied into rows 52 through 101, seat 3 into rows 102 through 151, and so on. The process is, (1) calculate the priority values for a seat on Working, then (2) copy those values to Priority Values Calculated, (3) calculate the priority values for the next seat. Repeat until all seats have been assigned. Rows 2 through 51 assign the first seat to each state for completeness.  (Row 1 contains the column header descriptions.) These 50 rows are kept at the top of the list by giving each a very large Priority Value, larger than the maximum Priority Value for a state for seat 2. I used 50,000,000. You may want to increase this number. I don’t recommend decreasing it.
3. Sort the data on Priority Values calculated by priority value (segment CSortRange). This is straightforward and very fast.
4. Assign the seats (segment DAssignSeats). Select seats based on priority values and fill in the seat number for that state (column E). Then fill in the House seat number (column F). You’ll see Table 3. Remember, rows 2 through 51 contain data for the first 50 seats, so we don’t need to look at those.

Priority values for seats 51 to 69. Click for larger image.

5. Find the largest number of seats for a state. Post that number to the Seats in Each State column in Priority Values Calculated. Repeat for the other 49 states. Reformat the Priority Values Calculated sheet so it looks pretty. Link the calculated priority values to column E on the Priority Values from Census sheet.

Conclusion

This is the first of what I hope will be a series of articles about this subject. And this is your big chance to be a published researcher. Think of an interesting question about apportionment. Use the software to do the calculations. Write up the results and send them to me (I prefer Microsoft Word, but can work with pdf). Or, if you’re VBA proficient, clean up my code. I will post your work here and, of course, give you full credit. Please, please document your changes and add comments in the code to mark the points where you made changes. Include your name and the date in your comment.

1. The multiplier for n = 1 is undefined (division by zero). I arbitrarily assigned a priority value of 50,000,000. This is by definition larger than any possible multiplier for n > 1. ↑
2. Census rounds both the multiplier and the priority values to 14 decimal places. I used Excel’s ROUND( ) to handle this. Any remaining differences are small enough to not worry me. If they bother you, see the conclusion above for a suggestion and/or cry for help. ↑

For reasons known only to the developers of this plugin, the vertical scroll bar does not work.  Please use the up and down arrows in the toolbar to move between pages.  Or, better yet, download the file.