I saw a spreadsheet with some VBA macros recently to calculate numbers of seats awarded in elections conducted using the d’Hondt system, so I thought I’d have a go at a single Excel function to do the same. WordPress doesn’t allow the Excel workbooks with VBA to be uploaded, so the file which can be downloaded from this link needs to have the following code added in a module
d’Hondt workbook – with VBA stripped
Public Function dHondt(iSeatsToAllocate As Integer, rVotes)
Dim dVoteTotal As Double
Dim i As Integer
Dim dVotes() As Double
Dim blnColumn As Boolean
Dim dSeatCostTrial As Double
Dim nSeatsTrial As Integer
Dim nParties As Integer
Dim iTries As Integer
Dim dUpperLimit As Double, dLowerLimit As Double
Select Case TypeName(rVotes)
aVotes = rVotes.Value
aVotes = rVotes
Err.Raise 1, "", ""
On Error Resume Next
blnColumn = UBound(aVotes, 2) <= 1
If Err.Number <> 0 Then 'single dim array
ReDim dVotes(UBound(aVotes) - LBound(aVotes))
For i = 0 To UBound(aVotes) - LBound(aVotes): dVotes(i) = aVotes(i + LBound(aVotes)): Next
ElseIf blnColumn Then
ReDim dVotes(UBound(aVotes) - LBound(aVotes))
For i = 0 To UBound(aVotes) - LBound(aVotes): dVotes(i) = aVotes(i + LBound(aVotes), LBound(aVotes, 2)): Next
ReDim dVotes(UBound(aVotes, 2) - LBound(aVotes, 2))
For i = 0 To UBound(aVotes, 2) - LBound(aVotes, 2): dVotes(i) = aVotes(LBound(aVotes), i + LBound(aVotes, 2)): Next
On Error GoTo 0
nParties = UBound(dVotes) + 1
For Each dVote In dVotes
dVoteTotal = dVoteTotal + dVote
dSeatCostTrial = dVoteTotal / iSeatsToAllocate ' - always high
dUpperLimit = dSeatCostTrial
While nSeatsTrial <> iSeatsToAllocate
iTries = iTries + 1
ReDim aSeats(nParties - 1)
nSeatsTrial = 0
For i = 0 To nParties - 1
aSeats(i) = Int(dVotes(i) / dSeatCostTrial)
nSeatsTrial = nSeatsTrial + aSeats(i)
If nSeatsTrial > iSeatsToAllocate Then ' adjust dSeatCostTrial up
If dSeatCostTrial > dLowerLimit Then dLowerLimit = dSeatCostTrial
ElseIf nSeatsTrial < iSeatsToAllocate Then ' adjust dSeatCostTrial up
If dSeatCostTrial < dUpperLimit Then dUpperLimit = dSeatCostTrial
If dLowerLimit = 0 Then ' before we have found any other lower limit
dSeatCostTrial = dSeatCostTrial * nSeatsTrial / iSeatsToAllocate
dSeatCostTrial = (dUpperLimit + dLowerLimit) / 2
If blnColumn Then
ReDim aSeatsTransposed(nParties - 1, 0)
For i = 0 To nParties - 1: aSeatsTransposed(i, 0) = aSeats(i): Next
dHondt = aSeatsTransposed
dHondt = aSeats
The function needs first the number of seats to allocate, and then an Excel row or column range with the votes cast, or percentages, so something like
The output is another Excel range, a row or column as the case may be, with the number of seats awarded under d’Hondt. Those curly braces are the result of the function being entered as an array formula – something to look up for those unfamiliar with these.
I’ve also written it so that it can be used more simply in VBA with just a single dimension array as the second argument.
Copyright? I’m really not too bothered, and I’m sure plenty of other people have done something like this, but yes, acknowledgement is appreciated.
23rd April, 2019
This is an argument for a scalable initiative to support volunteers who want to make their neighbourhoods look better. In summary, there should be some high quality, consistent but adaptable branding, to identify volunteer initiatives, and support communications with and about them. The initiative would be owned by a professional managed organisation, ideally operating at a national level. More details below in Recommendations Continue reading Signage for volunteer initiatives
Originally posted in the Sydenham Town Forum, 30 June, 2013
Last week’s uncharacteristic radio silence was thanks to cycling round Normandy. With my rusting French revived, I came across an interesting article in Le Monde on the way back, dealing with a report into the sorts of people who in 2010 gave German this new ‘word of the year’, and is now in official dictionaries. I know virtually no German, so I’ll have to go via the French to give the literal meaning in English as “angry citizens”, but on reading the article, it becomes clear the cultural meaning is NIMBY. For these
are not the young of the various Occupy movements and protesters in Turkey, Egypt or Brazil, but “they are for the main part people without children, working part time, with good school backgrounds, teachers and above all, those approaching retirement and the retired … More than a half say they are non religious, and a large majority say they don’t think Germany is a real democracy”
So – what to they do? Well, stop the redevelopment of the area round a station in Stuttgart, limit night flights in Frankfurt, and a new runway at Munich, stop proposed long term storage for nuclear waste by chaining themselves to railway lines, stop the electrification of the Munich-Zurich train line, get in the way of the high tension cables needed to bring electricity from wind farms in the north to the south, even get in the way of creating a national park in the north of the Black Forest.
Seems to me, these are just Germany’s Baby Boomer “usual suspects”, with too much time on their hands, and used to being able to get their own way, and for whom a real democracy is a system which delivers whatever they want.
I was going to title this blog ‘house prices, supply, rents, incomes and interest rates‘, but then wondered if any would be readers would still be awake.
The background is the discussion which has been going on for a while about whether UK house prices so high because of a lack of supply, but it also leads me to some thoughts about the linguistics of ‘rent’
Continue reading A former bond analyst thinks again about house prices
It is sometimes argued that policy changes which affect landlords can have no effect on the balance of supply and demand for tenants because the properties which may move between being rented and owner occupation will still exist, and will be occupied all the same. See for example these recent tweets Continue reading We need more evidence on tenure and occupancy
Reposted from my local Forum, 16 Jan, 2014, with a more helpful title, and some editing. It rambles a bit, but putting here now because it touches on various questions which arose yesterday (July 25, 2018) when I attended a consultation event on ‘De-risking’ growth in the Cambridge Milton Keynes Oxford arc.
I’m thinking about it now as much as raising the general problem of why landowners and other interested parties fail to co-operate.
The trigger here is this BBC interview with a member of the Chartered Institution of Water and Environmental Management (CIWEM) as reported here:
Back-to-nature flood schemes need ‘government leadership’
Continue reading Flooding, engineering, planning and politics
Rather than purchase land for some monetary amount, compulsorily or otherwise, and load local authorities / development corporations with debt, a South Anywhere County Development Corporation (SACDC) would issue equity to landowners whose land was affected by the JSP, in exchange for rights SACDC acquired over the land. Continue reading South Anywhere County Development Corporation / Equity for land rights funding
“Elder Stubbs” is an allotment site in East Oxford owned by a charity rather than the local authority. Something of its history can be found on its website here, but this skips the period in the 1990s when the charity found itself at loggerheads with Oxford City Council. For this I am grateful for the privately printed notes on the history of Elder Stubbs, written by John Purves, formerly Chair of the Elder Stubbs Trustees. Continue reading Elder Stubbs and Existing Use Value
we need an understanding that better ways are needed for setting rents on longer term tenancies, that these will need the buy in of landlords and their agents, and that they will involve an accurate reflection of local market rents. The history of their development in Germany suggests they were pioneered at a municipal level before being required nationwide by central government, but still operated locally. Even if the data used in the most sophisticated German municipalities is not immediately available for any UK local government, a lot of data will be available across different parts of government, and a better system than currently available for setting rents on longer term tenancies could be developed. It needs one local government to pioneer such an approach, and a department of central government to sponsor it.
Continue reading How to fix the rental market for real