Salut Sunderland’s Relegation Ready Reckoner

John McCormick: reckoning the odds

John McCormick: reckoning the odds

On a Friday, while the boss watches “Gardner’s world”, I usually plug into the Salut podcast and browse the SAFC world in general. But recently I’ve been keeping my spreadsheet skills up to date by practising inserting formulas so I thought I’d share this with you. It turned out to be a bit fiddly as I had to copy the spreadsheet formulae and then paste the lot into an HTML editor, which wasn’t as easy as it sounds, but it should work. If not I’m sure M. Salut will remove the post when he returns to duty.

To use the reckoner copy the table and paste it into the top left corner of an excel spreadsheet (starting the paste from square A1 should keep the formulae correct). Then type in the points gained for each game by clicking on the relevant cell and entering 1, 3 or zero. Go from the top downwards. It’s important to start from the top as some of the lower cells have formulae already entered and you don’t want to type over them. These formula will autocomplete some of the remaining cells, so e.g filling in the top cell for the Reading v QPR match (i.e. QPR’s points) will autocomplete the square for Reading’s points for the same game. Some of the squares are blank because these games only include one of the bottom teams. If you do find a formula comes up in the formula bar when you click on a cell in the spreadsheet then I suggest you leave it and move on to one which is empty. Generally speaking, the cells with a formula in will have a 3 in them at the start

(This has now been changed and will show a zero if you tweak the italics, see the comments below) .

This will change to reflect the actual points gained when you complete the complementary cell

You can add points as games progress or try predicting results. Have fun

 

 

Salut Sunderland’s end of season predictor:
game final points
team points 1 2 3 4 5
Villa SAFC Norwich Chelsea Wigan
34 =SUM(B5:G5)
QPR Reading Arsenal NUFC Liverpool
24 =SUM(B7:G7)
Reading QPR Fulham Man City W Ham
24 =IF(C7=””,0,if(c7=1,1,IF(C7=3,0,3))) =SUM(B9:G9)
SAFC Villa Southampton Stoke Spurs
37 =IF(C5=””,0,IF(c5=1,1,IF(C5=3,0,3))) =SUM(B11:G11)
NUFC Liverpool W Ham QPR Arsenal
37 =IF(E7=””,0,if(e7=1,1,IF(E7=3,0,3))) =SUM(B13:G13)
Stoke Norwich Sunderland Spurs Southampton
37 =IF(E11=””,0,if(e11=1,1,IF(E11=3,0,3))) =SUM(B15:G15)
Norwich Stoke Villa W Brom Man City
38 =IF(c15=””,0,if(c15=1,1,IF(c15=3,0,3))) =IF(D5=””,0,if(d5=1,1,IF(D5=3,0,3))) =SUM(B17:G17)
Soton W Brom Spurs Sunderland Stoke
39 =IF(D11=””,0,if(d11=1,1,IF(D11=3,0,3))) =IF(F15=””,0,if(f15=1,1,IF(F15=3,0,3))) =SUM(B19:G19)
Wigan Spurs W Brom Swansea Arsenal Villa
31 =IF(F5=””,0,if(f5=1,1,IF(F5=3,0,3))) =SUM(B21:G21)

 

 

 

 

 

Salut! Sunderland is written, illustrated and edited by - and principally for - supporters of Sunderland AFC. The site aims to be sufficiently literate and entertaining to appeal to people who do not follow SAFC but enjoy good football writing.

6 Responses to “Salut Sunderland’s Relegation Ready Reckoner” Subscribe

  1. Bill Harris April 26, 2013 at 10:58 pm #

    Why don’t you save us all a lot of work and provide us with some entertainment by filling in your predicted results for the rest of the season and then, each week, amending your predicted results to the actual result and printing out the results?
    If nothing else it would give you something to post after every set of matches.

  2. John Mac April 27, 2013 at 7:56 am #

    er….

    Nah!

  3. Plan B April 27, 2013 at 9:40 am #

    Or you could just use the predictor on bbc?

    • John Mac April 27, 2013 at 10:59 am #

      I like doing things for myself. It’s a bit like beer. I do buy it but I also make my own.

      And I like to solve the problems I then create. For beer it’s how do I just manage to have enough so I never run out without having so much it goes off. It’s a fine balance.

      With the spreadsheet it’s how do I get rid of the number threes that appear at the start, before the games have been played? (I do have a solution to try but you’re all welcome to post suggestions)

      The comparison ends with football. I do watch it but came to the concusion long ago that I can’t play

      • John Mac April 27, 2013 at 11:46 am #

        I’ve just done a tweak and changed the IF formulae to remove the number threes from the cells. Unfortunately, when it’s copied from the website and pasted into excel one of the ” marks in each formula isn’t accepted – it looks italicised on my machine.
        So if you want the predictor to work retype the double exclamation marks in each formula.

        (or use the BBC predictor)

        back to the beer!

  4. Plan B April 27, 2013 at 12:26 pm #

    Ah fair enough. I used to do something similar on excel years ago but I’d prob struggle now! Did the predictor a few Weeks ago and had us down but think we’ll stay up easily now, enjoy your beers in that knowledge 🙂

Leave a Reply

Promotion poll: Sunderland vie with Portsmouth but can Burton, Lincoln or Doncaster make top six?

Plenty of this from the Roker End last night.

John McCormick, associate editor, writes: things have quietened down with our ‘who to follow’ poll, as we might expect. Portsmouth, […]

McGeady: Aiden and abetting a new Sunderland promotion push

Jake: 'let's have a revolution - like winning some games'

Monsieur Salut writes: we all love a marquee signing, the arrival at the Stadium of Light of Light of a […]

A Salut! Sunderland welcome to Jordan WiIlis, Jack Ross’s third pre-season signing

Jake: 'sunnier times for Sunderland AFC'. Click the image to reach Salut! Sunderland's home page

Signing number three is another free, Jordan Willis, 24, a defender who played for Coventry City until the end of […]

Sunderland, Bolton and Bury. Our League One financial ramble has to end somewhere.

McCormick's Craic(featured image) full size 640 x 138

I didn’t know when I’d get back to this series but it seems appropriate to visit these three now, though […]

Page generated in 1.630 seconds. Stats plugin by www.blog.ca