I am fed up hearing about the PLP’s 9m votes mandate. A large number of these 9m votes will have voted for Labour candidates that lost. So I decided to calculate the number of votes cast for the PLP. So something for both political geeks and excel nerds. Here’s how I did it.

First I obtained a results file from the Electoral Commission. There is no good reason to cache this, and many bad ones. Their URL is

There is a risk that they’ll remove the link but I’ll solve that problem another day. The tab, helpfully named ‘Results for analysis’, is what we need. The Constituency meta data is in columns a-i, column j is blank, and columns k -eq hold the party results. I have inserted anew column at column k, so the results are held in L – ER.

  1. New Column J, =MAX(L2:ER2), displays the winning result, which I called Winner
  2. New Column K, =INDIRECT(ADDRESS(1,SUMPRODUCT((L2:ER2=[@Winner])*(COLUMN(L2:ER2))))), for the non title first row where @Winner is J2, see above, which I called winning party. The ADDRESS function returns row 1, i.e. the column title cell, of the column containing the max  votes. INDIRECT resolves the value of the column header. See here… for advice on using the address function for this purpose, and here… for advice on the indirect fucntion.

See the image below, which illustrates the analysis.

ec-vote-plus

 

I can now make a pivot table using the winning party and the sum of the winning votes aka winner, remembering to add Labour and the Co-Op votes.

By Party

I was challenged with the argument that a PR result would have returned a Tory/UKIP coalition, if people had voted the same. I looked at the Guardian, and Wikipedia on the GE and the NI results. I have sort of sought, to use EP categories for NI, the Unionist vote, Alliance, & SDLP with their sister parties and collecting the Left Parties into a Red Green Alliance as per the EP, anyway the votes and hence Parliament would look like this, if there was no threshold. my spreadsheet.

I suspect it would not have been a a Tory UKIP coalition and if so, it would have been very weak and relied upon the Unionist vote. As it happened UKIP ‘entered’ the Tory Party and the rest as they say is history.

4 Replies

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.