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
Option Explicit
Public Function dHondt(iSeatsToAllocate As Integer, rVotes)
Dim dVoteTotal As Double
Dim dVote
Dim i As Integer
Dim aSeats()
Dim aSeatsTransposed()
Dim aVotes
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)
Case "Range"
aVotes = rVotes.Value
Case "Array"
aVotes = rVotes
Case Else
Err.Raise 1, "", ""
End Select
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
Else
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
End If
On Error GoTo 0
nParties = UBound(dVotes) + 1
For Each dVote In dVotes
dVoteTotal = dVoteTotal + dVote
Next
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)
Next
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
End If
If dLowerLimit = 0 Then ' before we have found any other lower limit
dSeatCostTrial = dSeatCostTrial * nSeatsTrial / iSeatsToAllocate
Else
dSeatCostTrial = (dUpperLimit + dLowerLimit) / 2
End If
Wend
If blnColumn Then
ReDim aSeatsTransposed(nParties - 1, 0)
For i = 0 To nParties - 1: aSeatsTransposed(i, 0) = aSeats(i): Next
dHondt = aSeatsTransposed
Else
dHondt = aSeats
End If
End Function
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
={dhondt(Q$3,Q$4:Q$13)}
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.
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’
“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.
This is a final attempt to get TalkTalk to contact me, and provide my father with the broadband connection he is paying them for.
I struggle to find the words to describe how frustrating it is to talk to TalkTalk, but their customers seem to be locked into a Catch-22 – until the connection is working, you can only communicate by phone – they will not use email. So, if I try to connect from my father’s home, I get no further than this:
Broadband service not enabled
Contact provider for activation date
(Note to the right the wireless router, with TalkTalk branding, with all lights working; there is nothing wrong with this router,or connections to it from the PC)
but if I try to contact via a customer service from my own PC, using t this URL, https://myaccountholding.talktalk.co.uk/, it seems I have to be using a home TalkTalk connection:
So for months now I have been on the phone to TalkTalk call centres, sometimes getting through quickly, sometimes hanging on for what seems like hours – probably no more than 20 minutes, but it feels like hours – and each time being asked the same questions, and giving the same answers. Yes, I have switched the router on and off, more times than I care to remember.
To begin at the beginning, in December last year my father moved house, to an address two doors away from his old address. Before the move, TalkTalk had been providing both phone and broadband, and I assumed changing would be completely straightforward. Eventually the phone service came through, but not the broadband. I got on the phone, and after a lot of questions, I was assured that things had been sorted, and the broadband would resume at midnight; it did not.
So that he would at least be able to read emails, I approached a next door neighbour, who kindly shared the password to his wireless router, which could be detected through the wall, but explained that usage with his contact was limited. I didn’t suppose that, for the few days it would take to sort things out, our usage would cause a problem. So this other router was set up on my father’s PC as an alternative to connect to the internet.
It’s possible that all TalkTalk customers should keep a log of what phone calls they make, because here I can’t say for sure when I next spoke to them. I would expect that TalkTalk have some system for logging calls from customers, but they do not seem to be able to share this. Anyway, in due course, with the problem still not fixed, TalkTalk arranged to send an engineer to visit, explaining – over the phone – that the problem had either to be to do with the wiring of the router, or something to do with the wiring of my father’s new home. I was a bit sceptical, since it was the same router as had worked fine in the old house, and the new house had recently been rewired. But they gave a date and time slot when the engineer would arrive, and I made it down there in time, hoping to be able to ask the engineer what he found. I rang TalkTalk to get a more exact estimate of when the engineer would be arriving – but they didn’t know. Later my father came back from his shopping, and happily said – “so it’s all fixed?”. Apparently the engineer had called him to ask if it was OK for him to come early, and my father had agreed, but not asked what he had done, or any problems he had found. In fact, it seems he found no problem, so felt able to tell my father that everything was fine.
Well, when I switched the wireless connection back to his router, it wasn’t working, so I got on to TalkTalk, again, and again was told it was just a matter of waiting till midnight, and then the service would the there. So, I sent him an email, and asked him to check his emails next morning. Good news – he was able to read my email, and I thought everything was fixed.
I started to worry, however, when other family members visited, and reported being unable to connect to the internet via his router, and the suspicion crossed my mind that when he was checking emails now, or looking at website, his PC was connecting via the neighbour’s router, having it on a list of alternative routers to try if the first one failed. So, when I visited again yesterday, I had a look via the Control Panel – and yes, that was what was happening. So now I need to ask the kind neighbour if we have been running up unexpected bills for him – embarrassing. Of course I got on to TalkTalk – by phone – and was asked all the same questions, and gave all the same answers.
In retrospect, we should probably have switched from TalkTalk long ago, but I don’t want to have an interruption to the phone service while moving to another supplier, and up to a point, it seems more reasonable to suppose any supplier – even TalkTalk – will be able to work out what might be causing a problem, and fix it. I guess that’s why I’m writing this – in the hope that by putting something in the public domain, I can get TalkTalk to try to sort out the problem, rather than just leave it as some hassle which goes no further than a poor call centre underling somewhere in India.
I’m also curious to know how or why TalkTalk’s service can be so bad. Somewhere within the organisation there must be the information which will identify the problem. Do they simply economise on staff with sufficient training to deal with problems, and accept that customers who encounter problems will just shrug their shoulders, and move on to another supplier? I guess that is the basic business model justifying – to shareholders – the provision a poor service to customers.
More recently it struck me that TalkTalk must also log the actual broadband usage of its customers, since otherwise they would not know how to charge them. So they could identify how much broadband my father has used – which I suspect now is none.
