*Looking for a Sainte-Laguë calculator? Try here.*

Proportional representation is a great thing, because it allows political parties to have a fairer share of seats in a parliament/assembly/council in relation to their share of the vote rather than the rather unfair share of seats usually given by FPTP.

The only problem is that it’s often hard to calculate. The most common form is the D’Hondt method, by which seats are awarded in rounds to the party with the highest votes to seats ratio at the time. The co-efficient for this is calculated with this formula:

Co-efficient = Votes / (Seats already earned + 1)

Doing this over and over again to award each seat is a tricky and time-consuming process and, although perhaps better for understanding, isn’t ideal if you are looking to find results quickly. There are online calculators and Excel calculators out there, but most need to be paid for and are difficult to use.

However, I’ve built a simpler D’Hondt calculator for Microsoft Excel that can calculate the number of seats to be awarded to up to 6 parties. It can also take into account seats won via FPTP, which is important in doing seat calculations using Mixed-Member Proportional Representation such as the Additional Member System used here in Scotland.

All you need to do is enter the votes earned for each party in each region in the top left, configure the number of regions and seats awarded per region in the top right and then hit the Calculate button, which will automatically fill the table showing the number of proportional seats due. If you are just using a simple D’Hondt method you will only need to use one region. You can even enter the names of the parties and regions in the top left and it will replicate across to the other sections of the worksheet, which is useful if you plan on copying the data elsewhere. Here’s a screenshot of the workbook below:

There are further instructions inside the workbook on what to do. I’ve left the workbook open so that it’s entirely free to edit, and if you can use the VBA programming language you can edit the code and make amendments as you see fit. I’ve left as many comments within the code as possible to explain what everything does. If something goes wrong while using the Calculator, just download it again.

Just click here to download the D’Hondt Calculator (in Microsoft Excel Macro-Enabled format).

You can also check out the Sainte-Laguë calculator, which allows you to calculate proportional representation with another method.* *

If you have any suggested improvements or amendments to the calculator to suggest, feel free to leave a comment below.

*Has this resource been helpful to you? It would be greatly appreciated if you could donate a couple of dollars to keep the site up and running!*

UPDATE: 7th January 2015 – A recent Excel update has meant that for some users ActiveX buttons (such as the calculate button in this workbook) do not work as expected. Instructions on how to fix this error can be found here.

UPDATE: 8th September 2015 – The calculator now features the ability to award different numbers of seats per region (thanks to Ez for the suggestion!)

UPDATE: 27th March 2016 – You can now choose between a 6-party and a 10-party worksheet, making it easier to do calculations for larger party systems (thanks to Si for the suggestion!)

UPDATE: 5th June 2016 – You can now set a threshold for earning proportional seats

UPDATE: 15th June 2017 – You can now download a 12-party version of the calculator here https://www.stevenkellow.com/D’Hondt%20Proportional%20Calculator-v4.xlsm

UPDATE: 14th April 2019 – Some code improvements have been made and the 12-party/6-party version of the calculator is now the default

Hi,

I have a question about this calculator. Is it possible to make the number of seats from the region vary? From what I saw in the excel sheet, it is fixed to 7 for all regions.

Hi there,

Thanks very much for getting in touch!

That wasn’t something the calculator could do previously but I’ve made a few changes to the code and you can now award different numbers of seats per region. I’ve updated the file so if you download it again it should work, let me know if you have any more questions!

Helo Steven Pellow

I am writing a Bachelor thesis about what composition of the EU Parliament would result if uniform voting rules had been applied for the legislative term of 2014 until now. In order to be able to use your calculator I would have to be able to enter more parties, up to at least 10 if not more. Is there any chance that you could modify the code so that more parties can be included in the calculation?

Thank you and

Regards Si

Hi there Si,

That’s a great idea, thanks very much for the suggestion!

I’ve updated the file now so you can choose between a 6-party worksheet and a 10-party one, hope that works for you. Let me know if there’s any more I can do.

Best of luck with your thesis!

Regards,

Steven

I want to see the formula that it is used for allocation of seats

Hey M,

Here’s the VBA code that runs when you hit the Calculate button, let me know if you need any more information!

`Dim seatsAward As Integer ' The number of seats to award for each region`

Dim seatsIncrement As Integer ' A counter that checks each region's seat award

Dim max As Double ' The maximum coefficient to determine which party wins the seat

Dim votes(0 To 5) As Double ' The array storing votes, set to a double so that predictions with decimals can be used

Dim seats(0 To 5) As Integer ' The array storing seats already earned

Dim coeff(0 To 5) As Double ' The array storing the coefficients

Dim awarded(0 To 5) As Double ' The array storing the number of proportional seats awarded

Dim row, regions As Integer ' Setting the row used for locational purposes and the number of regions to be used

Range("$J$14:$O$21").Clear ' Clears the proportional seats awarded in case there's already data

regions = Cells(4, 15).Value ' Gets the number of regions from cell O4

row = 14 ' Sets the row location for getting other data

seatsIncrement = 3

For region = 1 To regions ' A loop that runs for each region

seatsAward = Cells(seatsIncrement, 9).Value ' Gets the number of seats to award per region from column I

If seatsAward < 1 Then ' Conditional loop to use default from cell O5 if not specific one chosen seatsAward = Cells(5, 15).Value End If seatsIncrement = seatsIncrement + 1 votes(0) = Cells((row - 11), 2).Value ' Gets the number of votes for the first party votes(1) = Cells((row - 11), 3).Value votes(2) = Cells((row - 11), 4).Value votes(3) = Cells((row - 11), 5).Value votes(4) = Cells((row - 11), 6).Value votes(5) = Cells((row - 11), 7).Value For Validate = 0 To 5 ' If there are no votes then sets the votes to 1, so that there's no errors - will not affect seats unless votes are extremely low If votes(Validate) < 1 Then votes(Validate) = 1 End If Next seats(0) = Cells(row, 2).Value ' Gets the number of seats already earned by a party for Mixed-Member or Additional member systems seats(1) = Cells(row, 3).Value seats(2) = Cells(row, 4).Value seats(3) = Cells(row, 5).Value seats(4) = Cells(row, 6).Value seats(5) = Cells(row, 7).Value For counter = 0 To 5 ' A loop that calculates each party's coefficient coeff(counter) = votes(counter) / (seats(counter) + 1) Next For awardcounter = 1 To seatsAward ' A loop that awards the number of seats available for each region max = 0 ' Sets the max to 0 to start the loop For coeffcounter = 0 To 5 ' A loop for each party to check if it has the maximum coefficient If coeff(coeffcounter) > max Then

max = coeff(coeffcounter)

End If

Next

For seatcounter = 0 To 5 ' A loop that increments the number of seats awarded

If max = coeff(seatcounter) Then ' If the party has the largest co-efficient add a seat

awarded(seatcounter) = awarded(seatcounter) + 1

coeff(seatcounter) = (votes(seatcounter) / (1 + seats(seatcounter) + awarded(seatcounter))) ' Update the coefficient for the next loop

End If

Next

Next

Cells(row, 10).Value = awarded(0) ' Output the number of seats awarded for this region

Cells(row, 11).Value = awarded(1)

Cells(row, 12).Value = awarded(2)

Cells(row, 13).Value = awarded(3)

Cells(row, 14).Value = awarded(4)

Cells(row, 15).Value = awarded(5)

row = row + 1 ' Move to the next row for the next region

Erase awarded() ' Clear the number of seats awarded to each party as it's irrelevant for the next region

`Next ' Continue to the next region`

Thank You for showing the code, I do not understand some parts of the code if could explain ?

The part that i don understand are:

Dim votes(0 To 9) As Double ‘

Dim seats(0 To 9) As Integer ‘

Dim coeff(0 To 9) As Double ‘

Dim awarded(0 To 9) As Double

When we declare this variables why you have done 0 To 9,

Then Why

seatsIncrement = 3 why you increment the seats by three?

votes(0) = Cells((row – 11), 2).Value ‘ Gets the number of votes for the first party

votes(1) = Cells((row – 11), 3).Value

votes(2) = Cells((row – 11), 4).Value

votes(3) = Cells((row – 11), 5).Value

votes(4) = Cells((row – 11), 6).Value

votes(5) = Cells((row – 11), 7).Value

votes(6) = Cells((row – 11), 8).Value

votes(7) = Cells((row – 11), 9).Value

votes(8) = Cells((row – 11), 10).Value

votes(9) = Cells((row – 11), 11).Value

This code above as i understand is getting the votes of 10 parties in 8 regions am i Right?

Then the thing that i do not understand is why row-11 and etc

I hope you answer my questions

Sincerely

Thank YOu!

Hey,

The 0 to 9 is to make sure that there’s 10 different parts in the array – most programming languages start with 0 as the first entry.

The seatsincrement variable is just telling the code where the number of seats to award is stored in the spreadsheet, it doesn’t actually increment anything.

You’re right, the votes(x) arrays get the number of votes for each party in a given region, the row-11 bit is again just a way of making sure we’re getting the data for the right part of the spreadsheet.

Hey do you have the code for another method like Sante Lague?

Yeah sure, check out the Webster/Saint-Laguë calculator here.

You are great men, Thanks

Hey , which part of the code has to be changed, if i want to calculate the number of seats instead of having 8 regions to replace it in 1 region, maybe there will be a difference, having one regions instead of having 8 or more or less ?

For example in the one sheets that has with multiple regions in another sheet can you update to use in one region? For the d’Hondt and Webster/Sante Lague Method

What do you think?

Each region is calculated independently of each other, so there’ll be no difference to each region total – but if you’re talking about combining regions into one then there will be a difference. To combine regions save your data in another speadsheet and find the sum of the votes and enter them into one row of the calculator.

If you want to calculate different totals for either of the calculators simply delete the data and start again or save another copy of the spreadsheet 🙂

Hi , there are 12 regions in my country and trying to modify the file , but without success. Can you help me please?

Hi Paul,

I’ve created a 12-party version for you to download here: https://www.stevenkellow.com/D%27Hondt%20Proportional%20Calculator-12party.xlsm

If it’s really helped you out please consider a donation to the PayPal link above, thanks!

Hey Steven!

I find that the 12-party D’Hondt calculator doesn’t perform the calculations for Party 11 and 12.

Just wanted to bring it to your attention.

Regards.

Tobias

Thanks for letting me know, should be fixed now 🙂

Would it be possible to change the number of regions? In particular, I’m thinking of using this to model proportional representation of the American electoral college system (with 51 regions.)

Hi Kari, you’ll be able to use the existing version to calculate the results of any given region/state, and then be able to combine them at the end by keeping a record with them in another sheet. A single-sheet solution like this might be something you would need though if you were looking to award votes nationally as well as for each region, is that something you’d want to do?

Yeah, it’s possible. I’m just trying to figure out how best to handle 51 regions in one shot — without having to copy/paste/copy/paste eight regions at a time every time we want to model different potential election results.

Hi Kari, no problem – I’ve made a copy of the sheet for you here with 51 regions, should hopefully work just the same, let me know what you think!

Thanks!

Great work Steven

Would it be possible to vary/add the votes per region instead of just one total amount, keeping the threshold or even having different thresholds per region? Similar to the way you can have different number of seats per region. So instead o 1 column for number of seats you’d have 3 optional columns. One for seats, one for number of votes, one for threshold.

Thanks a lot for this simple, yet great, spreadsheet