Excel D’Hondt Proportional Representation Calculator

Download

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

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License, which allows you to share and adapt the material for non-commercial purposes as long as attribution is provided and that the same license applies to your work.

 

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’Hondt%20Proportional%20Calculator-v6.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

UPDATE: 14th May 2021 – Fixed some logic errors for calculating seats with a threshold

34 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!

      1. Hi Veysel,

        You should be able to add the columns into the spreadsheet and then update the source code in Developer tab > Source code. You’ll need to update the cell references that are 12 apart to being 15 apart.

  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. Hi , there are 12 regions in my country and trying to modify the file , but without success. Can you help me please?

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

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

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

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

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

    1. Hi Mehmet,

      There’s no limits for the seats for each region, it can be as high as you’d like. If the number of seats is different between regions you can enter the different numbers in the “Seats to Award (optional)” column and it’ll take that into account.

      Hope this helps!

  9. Hi there. Your d’Hondt calculator is fantastic – thanks for making it available.
    I was just wondering whether it would be possible to make the excel file so that it displays the number of votes by party in each of the rounds?
    Returning the total number of proportional seats gained in each region very quickly is a big help, but it would be very handy to “show the workings” so to speak, in order to see, for example, the number of votes a party was short of gaining a proportional seat. I’ll obviously make a donation for your time!
    I’d appreciate any help!

    1. Hi Joe,

      Thanks very much for the feedback, it’s much appreciated. That’s a great suggestion and I’ve implemented it now in the latest version of the calculator (v6 – should be able to download from the links in the main post above).

      There are now two new sections, one that simply shows at which round each seat was awarded, and then another which shows the coefficients for each round of the calculation (which looks a bit more messy but as good as can be done in Excel).

      Hope this assists you but if you have any questions let me know

  10. Hi Steven,
    the calculator is brilliant I was about to start work on something similar and was probably going to do this as a Python project (for my own personal development using Python)… but, nah, yours works so well I won’t bother.
    That said, would it be possible if I tailored this to an upcoming election using 2016 data (making sure that you are credited with the original work btw) and made a few tweaks, could I forward the altered version to a few people so they can look at scenarios ?
    regards,
    Ally

    1. Hi Ally,
      Thanks very much! I’d be more than happy for you to make use of the calculator in that way, I’ve added a Creative Commons license to the work there to be clear that such use and adaptation is allowed, hope it helps!

  11. Hi Steven

    The calculator is very good. I have been using it to do some analysis of UK General Election Results.

    I have been trying to use the threshold option, which doesn’t seem to work whatever value I set it to.

    Can you explain how to use it?

    1. Hi Mike,

      Thanks very much, glad you are finding it useful. I’ve played about with the logic in the code there and the threshold should work more reliably now, if you can download the version from above (should be v7) it should hopefully work!

Leave a Reply to M Cancel reply

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