Is anyone any good with spreadsheets able to help me?
I have a spreadsheet I am working on,
I have two tables, one for every game we have played this season and one for collective player stats (I do not intend to infringe on the statbank with this btw)
Table one looks like this:
Table two like this:
Now my problem is that Goals Scored column in sheet 2. I am using the COUNTIF Formula to highlight the whole goalscorers column and provide a count of cells with "*Aneke*" in. But Aneke being a good scored two goals against Rochdale... How do I count that extra goal if it's already in a cell that contains his name once?
Actual formula used
=COUNTIF(I3:I49, "*Aneke*")
Is there a way to count instances of Aneke as opposed to cells containing Aneke or Washington who also scored two vs Fleetwood?
Is anyone any good with spreadsheets able to help me?
I have a spreadsheet I am working on,
I have two tables, one for every game we have played this season and one for collective player stats (I do not intend to infringe on the statbank with this btw)
Table one looks like this:
Table two like this:
Now my problem is that Goals Scored column in sheet 2. I am using the COUNTIF Formula to highlight the whole goalscorers column and provide a count of cells with "*Aneke*" in. But Aneke being a good scored two goals against Rochdale... How do I count that extra goal if it's already in a cell that contains his name once?
Actual formula used
=COUNTIF(I3:I49, "*Aneke*")
Is there a way to count instances of Aneke as opposed to cells containing Aneke or Washington who also scored two vs Fleetwood?
so you count the all instances of his name then add on the appropriate number of additional goals where they crop up. However it's likely to be affected by formatting quirks in the data, such as whether you leave a space between the name and number of goals scored. Unfortunately I can't test it as my spreadsheet software doesn't recognise the wildcards as wildcards.
It's probably safer (if a bit of a pain in the arse at this stage of the season) to reformat the sheet to put GS, GC and Pts after the sub info and then have columns for GS1, GS2, GS3 etc and AS1, AS2, AS3 etc. That way you just put each individual scorer in the relevant cell, and set it to count the cells in the ranges without worrying about any wildcard business. You might want to add a few extra columns just in case we tonk someone at some point, so you don't have to amend all the formulas again, and then once you've sorted out the basic functionality just hide columns GS6-GS9 and AS6-AS9 so you don't snort with derision at the optimism of their existence every time you open the sheet.
So if the goal scorer data is now in columns I to Q rows 3 to 49 on sheet 1, and the individual player data starts with Aneke in cell A2 on sheet 2 then you can use:
=COUNTIF(Sheet1.$I$3:Sheet1.$Q$49; A2)
so it's looking to see what is in cell A2 on the current sheet, and counting all the instances of that value on sheet one between I3 and Q49. I've tested it, and this one definitely works. The advantage of doing it this way is that you can then fill down on the current sheet for all the other players rather than having to hard code the names in the formulas, while the dollar signs fix the reference range that they're looking at so that doesn't get shifted too when you move down the column. (I appreciate I may be teaching you to suck eggs on this point, but am including the explanation for any masochistic bystanders).
I find the ppg table really interesting, as the only way to see a true representation of where we are in the table
Personally I think the artificial nature of us staying in the top 6 for a long time has masked our poor results. Similarly the hyperbole about Man City's poor start to the season was massively exaggerated because they had played fewer games.
The really good news if that our actual position is nigh on the same as our PPG position. The bad news for those that backed us, on the basis of buying money, is that we look more likely to finish in the bottom half of the table.
How we can go from 22 points from 10 games to then getting the same number of points from the next 21 matches is absolutely bizarre - it's almost as if someone flipped a switch and we've been playing in the dark since that winning run.
Looks like you'll need at least mid 70's points wise to get into the play offs - and that might only get you 6th and 80 points for 5th and above. So, from our remaining 11 games we'll need at least 7 wins & 4 draws - and NO defeats. Or 8 wins and a couple of draws - and ONE defeat.
Looks like you'll need at least mid 70's points wise to get into the play offs - and that might only get you 6th and 80 points for 5th and above. So, from our remaining 11 games we'll need at least 7 wins & 4 draws - and NO defeats. Or 8 wins and a couple of draws - and ONE defeat.
not going to happen.
There will likely be a small reversion to the mean so 72-74 points is likely to be enough for 6th - it has been for the last number of seasons where a full season was completed. We'd be unlucky to miss out on 72 points.
If it's higher than that, then we're in an unusual year (no sh*t we are)...
20 points from 11 games: six wins, two draws, three defeats.
Looks like you'll need at least mid 70's points wise to get into the play offs - and that might only get you 6th and 80 points for 5th and above. So, from our remaining 11 games we'll need at least 7 wins & 4 draws - and NO defeats. Or 8 wins and a couple of draws - and ONE defeat.
not going to happen.
There will likely be a small reversion to the mean so 72-74 points is likely to be enough for 6th - it has been for the last number of seasons where a full season was completed. We'd be unlucky to miss out on 72 points.
If it's higher than that, then we're in an unusual year (no sh*t we are)...
20 points from 11 games: six wins, two draws, three defeats.
But in your calculation you've gone for the lower mean figure of 72 rather than 74 and missed the point that our GD is far far worse than any of the other top 6 sides.
Therefore, a points total of around 75 might be needed by us to secure 6th, which means another 23 points. So needing 7 wins & 2 draws. Slightly better than my original post I grant you but something still totally out of reach imo.
Comments
Hull will actually be the team to have played the most games tonight. A loss will take them to
Doncaster & Accrington will still have 2 in hand on us after tonight.
Don't leave us hanging Dazzler
I have a spreadsheet I am working on,
I have two tables, one for every game we have played this season and one for collective player stats (I do not intend to infringe on the statbank with this btw)
Table one looks like this:
Table two like this:
Now my problem is that Goals Scored column in sheet 2. I am using the COUNTIF Formula to highlight the whole goalscorers column and provide a count of cells with "*Aneke*" in. But Aneke being a good scored two goals against Rochdale... How do I count that extra goal if it's already in a cell that contains his name once?
Actual formula used
Really annoying that doing a ctrl F in that column it returns the correct number!
If not this was the last one and I am including the GPG and CPG for each side too
Personally I think the artificial nature of us staying in the top 6 for a long time has masked our poor results. Similarly the hyperbole about Man City's poor start to the season was massively exaggerated because they had played fewer games.
How we can go from 22 points from 10 games to then getting the same number of points from the next 21 matches is absolutely bizarre - it's almost as if someone flipped a switch and we've been playing in the dark since that winning run.
Accrington wasted theirs, will Blackpool do the same?
A minor formatting correction to your table, 4 teams get relegated from L1.
adapted for future.
not going to happen.
If it's higher than that, then we're in an unusual year (no sh*t we are)...
20 points from 11 games: six wins, two draws, three defeats.
Therefore, a points total of around 75 might be needed by us to secure 6th, which means another 23 points. So needing 7 wins & 2 draws. Slightly better than my original post I grant you but something still totally out of reach imo.
Everything still to play for. A win tomorrow and our PPG expected points will increase from 70 to 72 and we're right back in the mix.
And guess what... tomorrow's fixtures: Pompey v Ipswich & Oxford v Blackpool. Points will be dropped around us.
Ipswich 73
-
Blackpool 72
Oxford 72
Portsmouth 70
Charlton 70