Excel D’Hondt Proportional Representation Calculator

Download

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:

excel-calculator

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!

Donate via PayPal

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%27Hondt%20Proportional%20Calculator-12party.xlsm

17 thoughts on “Excel D’Hondt Proportional Representation Calculator

  1. 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.

    1. 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!

  2. 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

    1. 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

    1. 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

  3. 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!

    1. 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.

  4. 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?

    1. 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 🙂

  5. 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

Leave a Reply

Your email address will not be published. Required fields are marked *