{"id":2940,"date":"2016-08-06T18:13:43","date_gmt":"2016-08-06T18:13:43","guid":{"rendered":"http:\/\/wiki.davelevy.info\/?p=2940"},"modified":"2024-08-15T08:40:43","modified_gmt":"2024-08-15T08:40:43","slug":"analysing-the-2015-general-election","status":"publish","type":"post","link":"https:\/\/davelevy.info\/wiki\/analysing-the-2015-general-election\/","title":{"rendered":"Analysing the 2015 General Election"},"content":{"rendered":"<p>I am fed up hearing about the PLP&#8217;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&#8217;s how I did it.<!--more--><\/p>\n<p>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<\/p>\n<ul>\n<li><a href=\"https:\/\/web.archive.org\/web\/20190728214121\/https:\/\/www.electoralcommission.org.uk\/our-work\/our-research\/electoral-data\">http:\/\/www.electoralcommission.org.uk\/our-work\/our-research\/electoral-data<\/a><\/li>\n<\/ul>\n<p>There is a risk that they&#8217;ll remove the link but I&#8217;ll solve that problem another day. The tab, helpfully named &#8216;Results for analysis&#8217;, 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 &#8211; ER.<\/p>\n<ol>\n<li>New Column J, =MAX(L2:ER2), displays the winning result, which I called Winner<\/li>\n<li>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\u00a0 votes. INDIRECT resolves the value of the column header. See <a href=\"http:\/\/www.mrexcel.com\/archive\/Formulas\/26299.html\">here&#8230;<\/a> for advice on using the address function for this purpose, and <a href=\"http:\/\/stackoverflow.com\/questions\/11401624\/function-which-takes-address-and-gives-value\">here&#8230;<\/a> for advice on the indirect fucntion.<\/li>\n<\/ol>\n<p>See the image below, which illustrates the analysis.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2941\" src=\"https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2016\/08\/ec-vote-plus.png\" alt=\"ec-vote-plus\" width=\"650\" height=\"157\" srcset=\"https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2016\/08\/ec-vote-plus.png 650w, https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2016\/08\/ec-vote-plus-300x72.png 300w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>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.<a name=\"party\"><\/a><\/p>\n<h3>By Party<\/h3>\n<p>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 <a href=\"https:\/\/www.theguardian.com\/politics\/ng-interactive\/2015\/may\/07\/live-uk-election-results-in-full\">the Guardian<\/a>, and Wikipedia on<a href=\"https:\/\/en.wikipedia.org\/wiki\/2015_United_Kingdom_general_election\"> the GE<\/a> and <a href=\"https:\/\/en.wikipedia.org\/wiki\/2015_United_Kingdom_general_election_in_Northern_Ireland#Full_results\">the NI results<\/a>. I have sort of sought, to use EP categories for NI, the Unionist vote, Alliance, &amp; 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. <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/18PryzeCGiJLZvXQ7p7w4EX9b7WjLuPOy\/edit?usp=share_link&amp;ouid=104184060400374865985&amp;rtpof=true&amp;sd=true\">my spreadsheet<\/a>.<\/p>\n<p><a href=\"https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2016\/08\/ge-2015-vote-share.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6966\" src=\"https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2016\/08\/ge-2015-vote-share.png\" alt=\"\" width=\"750\" height=\"512\" srcset=\"https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2016\/08\/ge-2015-vote-share.png 750w, https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2016\/08\/ge-2015-vote-share-300x205.png 300w\" sizes=\"auto, (max-width: 750px) 100vw, 750px\" \/><\/a><\/p>\n<p>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 &#8216;entered&#8217; the Tory Party and the rest as they say is history.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am fed up hearing about the PLP&#8217;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&#8217;s how I did it.<\/p>\n","protected":false},"author":1,"featured_media":2415,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_share_on_mastodon":"0"},"categories":[3],"tags":[156,1059,1060,141,911,1061,1062],"class_list":["post-2940","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology","tag-excel","tag-ge2015","tag-labour-vote","tag-politics-2","tag-technology","tag-winning-vote","tag-winning-vote-by-party"],"share_on_mastodon":{"url":"","error":""},"jetpack_featured_media_url":"https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2015\/01\/voting-london-2011.png","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts\/2940","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/comments?post=2940"}],"version-history":[{"count":2,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts\/2940\/revisions"}],"predecessor-version":[{"id":11262,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts\/2940\/revisions\/11262"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/media\/2415"}],"wp:attachment":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/media?parent=2940"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/categories?post=2940"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/tags?post=2940"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}