Top goal scorers spread sheet. Cant figure out how to calculate Goal streaks

Hello. I've been keeping track of all our goal scorers from a local 5-aside futsal league.

I want to make it more interesting by adding in 'goal streaks'. So if a player scores in week 1, week 2, week 3 and week 4 it will display in a column next to their name saying they have scored in 4 consecutive games. as soon as they fail to score in a game (record a 0) then the goal streak column should reset to 0. However some weeks a team will be on their week off so I want blank cells to be disregarded and not break the streak.

The league will be going on for around 15 weeks possibly longer so the data will need to continue indefinably if possible.

I've been looking around this forum and i haven't found anything that exactly suits my needs, a lot of formulas I've seen are in columns or only work with wins or losses etc.

Also help automating the 'position' coloumn would be great + a way to organise alaphbeticazlly when two players have scored the same amount would be great.

There is no fomulas being used at all currently, Ive been entering all the data manually and using the sort & filter > Largest to smallest function.



First time posting on the site, any help would be really appreciated

Cheers & Regards,
Lewis

It's rather odd to want to add columns in the middle of the data. So, I have rearranged it to look more rational. Don't rearrange the data in the tabel every time. let Excel do it for you.

Streak (d4, copied down is given by an array formula:

=MAX(FREQUENCY(IF(F4:Z4<>"",COLUMN(F4:Z4)),IF(F4:Z4="",COLUMN(F4:Z7))))

I'd amend the scores to make them unique =SUM(F4:Z4)+ROW()/10^10 by adding a very small number to each one.

The table in green sorts the players into ranking order.

Thanks for your help with this Glenn, that looks great!

The only problem is I think that's showing the longest goal streak (which is cool, I want to keep that!). I wanted a current goal streak column which breaks and reset back to 0 if they record a 0 in a match week. If the cell is blank then it doesn't break the streak. Is this possible? Also how can I display the week by week goals next to the totals in the green table. Players like to see how many hattricks etc they have scored so I like to share this too. A last 5 games(tweakable amount) would be cool.

Also if two players are tied can they both have the same rank but still keep the alphabetical ordering?

The players in my league are loving the top scorers list and it creates a lot of talking points. If the current sprees could start at 5 and count up from there that would be good or maybe even just highlight a players name if they are on a spree of 5 or above. this is not very important though the way it is, is fine.

Additionally I have been highlighting the top scorer(s) goal tally for that week just manually at the moment is there a way to automate this?

There is also a league table I made on that tab. I imported artwork + logos for each team and then overlayed text boxes to make a nicer looking table. Its worked very well so far although I currently have to move the logos for each team manually. While this not a problem at the moment as it doesn't take long, If our league we're to grow and more teams enter it could become one. Mistakes can also be made matching the logos to teams manually, is there a way to do it automatically?

Im not sure I undertand what you mean by this
"I'd amend the scores to make them unique =SUM(F4:Z4)+ROW()/10^10 by adding a very small number to each one."

I really appreciate the help with my workbook! I'm not great with excel.

Cheers & Regards,
Lewis

I forgot about this... Take a look now. If it's OK, I'll explain any bits you don't understand. If there's a no-score, you'll have to enter NS rather than leaving it blank.

WRT SUM(F4:Z4)+ROW()/10^10...

in order to make the matching of names with scores easier, it is necessary to make every score unique. I did this by adding the row number divided by 10,000,000,000 to each score. It makes no material difference to the score, but does make every one unique. The formulas then match the correct name against the correct scores.

Glenn - thanks mate, your awesome.

Its looking great! The current streak column is working perfectly which is fantastic.
The only problem I can see is with the Longest streak column and how that is working with 'weeks off'. Currently if I leave a cell blank it breaks the 'longest streak' and it will start counting from 0 again, while with the current streak column a blank cell will be skipped, not break the streak and also not count towards the steak which is perfect

If the longest streak could work like the current streak that would be perfect! At the moment if a player scores every week for 4 weeks they have a longest steak of 4 correctly but if their team is not playing in week 5 then their longest streak breaks through no fault of their own. I can put a 0 instead of leaving the cell blank to stop it breaking but then this counts towards the streak, so if the player scores in weeks 1-4, then has a week off in week 5 (i put in a 0 to stop the streak breaking) then they score again on week 6 it will display a longest streak of 6 (when it should be 5) and a current streak of 5 (which is correct).
Does that make sense and is their anyway to fix it so blank cells are skipped (or instead of blank cells a NA or somthing), they neither break the longest streak or add to it?

Other than that it is perfect. I have added extra columns in =INDEX(G$4:G$48,MATCH($F54,$F$4:$F$48,0))and dragged it across and down to show the weekly totals in your green table as people like to see the week by week scoring as well as the grand totals. This seems to be working perfectly.
I've also rearranged the data above the green table into alphabetical team order which will make entering the scorers for each team much easier, and then ill just share the green table with the players which ranks them all perfectly.

Did you have a look at the league table tab? Is there a way to get the team logos to automatically move up and down the league table with the correct team name? At the moment I have to manually move these into the correct place.

This is already a massive improvement on what I had before, I think the unique numbers thing you mentioned makes sense to me now. If we could get the Longest streak coloumn working correctly that would be the icing on the cake!
you forever have my gratitude

Cheers & Regards,
Lewis Wheeler

Originally Posted by Ldouble

one last thing, Is it possible to use a 0 instead of NS? This isnt much of a problem its just a table with all numbers looks better. 0 is just a neater way of writing none scored but if it has to be NS then that doesn't matter too much.

I fear you're going to have to overcome your aesthetic sensibilities... At my pay grade (did I just write that??? I hate cr@ppy office-speak)... I couldn't get it to work with numbers. The problem is the skipped matches. If you use numbers >0 for scores, you can use 0 for non-scores, but you can't use blanks for missing matches.

If you do, Excel (and me) gets confused betwen missing matches and matches yet to be played. I tried a couple of workarounds and they didn't want to co-operate. So. You have a number for a score (>0, obviously); N for a no-score and A for absent (non-participant). It seems OK to me.

BtW, it REALLY doesn't like stray spaces entered in the "matches to come" columns. To avoid strange results, i suggest that you hide the empty columns, until you need them. I'll take a look at your other Q presently.

Hi Glenn

Thanks again, its fine to use the letters. It looks like its working perfect apart from the Longest streak of scores column.

Like I mentioned yesterday, the problem with that column is with the week off's. For example, myself (Lewis Wheeler): I have scored in every week so far and its correctly showing a current streak of 4 and a max streak of 4.
Tomorrow (game day) however our league can only play 2 matches due to a volleyball tournament so a lot players, including myself cant play in week 5. With the work book how it is currently my longest streak is going to 'break' tomorrow and reset back to 0 by the time I get to play again in week 6.

What I need is to be able to continue the longest goal streak over the week off (week 5 in this case), without adding to the goal streak (which is what putting in a 0 does). Maybe this could be done by using a 'S' for skip or something and changing the current streak back to 'ns' not any text?
So at the moment I can either put 0's in place of the weeks off and not break the streak (+ keep goal totals accurate) but this will inflate everyone longest streaks + current streaks by +1 or leave the cell blank and let it break the longest streak every time there is a week off by leaving the cell blank.

Its worth noting that the current streak's is working perfectly because if I leave 'week 5' blank for Lewis Wheeler, then add goals into week 6 it correctly displays a streak of 5. With the sheet how it is I will probably hide the longest streak for now until we can get it fixed and just go with the current streak column when I publish the top scorers on Monday / Tuesday next week. Unless we can get it fixed in time.
Just a thought: maybe the longest streak can be fixed by taking its value from the current streak. something like IF 'current streak cell' is bigger than 'longest streak cell' use current streak value as longest streak value. This would have the disadvantage of giving wrong values if I accidentally entered the scores wrong (it wouldn't fix itself when I correct the scores) but at least it would work with the weeks off correctly.

Like always Glenn I really appreciate the help.
You should be on Salary Grade 10+ in my eyes mate, your a legend.

Cheers & Regards,
Lewis