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) | ||||||
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 🙂
Or you could just use the predictor on bbc?
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
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!
er….
Nah!
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.