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