Hi there,
I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a solution to my problem and can't find exactly what I'm looking for, so apologies if this has already been asked in the near past.
I am looking to calculate the longest and current streaks of Wins and Losses for my pool team players. The problem is that players may not play every week, but just because they don't play, this shouldn't interfere with their streaks. Therefore if a player wins two weeks running, then misses a week, and then wins the next two, his winning streak is still 4 over those five weeks. The streaks should only be restarted after a change in W or L.
I've set out an example below. This shows three players records over a 15 week period (my actual spreadsheet is for 36 weeks), although currently we have only played up to Week 11. Obviously W and L indicate a Win or Loss that week, whereas X indicates they did not play that week. All of these W's, L's and X's are automatically inserted into these cells by a formula dependent on data from elsewhere in my workbook. I am looking for formulas to go into columns Q, R & S, and I have indicated what the results should be.
Note that the array should always be B:P, i.e. all weeks up to the end of the season irrespective of whether the games have been played or not. Results are updated weekly and therefore the streaks need to automatically change as the data is populated into the new weeks.
Please also note that I am looking for formulas only. I can't do VBA!!
I hope this is clear, and I appreciate any help you can give.
I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a solution to my problem and can't find exactly what I'm looking for, so apologies if this has already been asked in the near past.
I am looking to calculate the longest and current streaks of Wins and Losses for my pool team players. The problem is that players may not play every week, but just because they don't play, this shouldn't interfere with their streaks. Therefore if a player wins two weeks running, then misses a week, and then wins the next two, his winning streak is still 4 over those five weeks. The streaks should only be restarted after a change in W or L.
I've set out an example below. This shows three players records over a 15 week period (my actual spreadsheet is for 36 weeks), although currently we have only played up to Week 11. Obviously W and L indicate a Win or Loss that week, whereas X indicates they did not play that week. All of these W's, L's and X's are automatically inserted into these cells by a formula dependent on data from elsewhere in my workbook. I am looking for formulas to go into columns Q, R & S, and I have indicated what the results should be.
Note that the array should always be B:P, i.e. all weeks up to the end of the season irrespective of whether the games have been played or not. Results are updated weekly and therefore the streaks need to automatically change as the data is populated into the new weeks.
Please also note that I am looking for formulas only. I can't do VBA!!
I hope this is clear, and I appreciate any help you can give.
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |
1 | WEEK 1 | WEEK 2 | WEEK 3 | WEEK 4 | WEEK 5 | WEEK 6 | WEEK 7 | WEEK 8 | WEEK 9 | WEEK 10 | WEEK 11 | WEEK 12 | WEEK 13 | WEEK 14 | WEEK 15 | LONGEST W STREAK | LONGEST L STREAK | CURRENT STREAK | |
2 | John | W | W | X | W | W | L | X | L | W | W | W | 4 | 2 | W3 | ||||
3 | Peter | X | W | L | L | W | X | X | W | X | W | W | 4 | 2 | W4 | ||||
4 | George | X | L | L | X | L | W | X | L | X | L | X | 1 | 3 | L2 |
Last edited by a moderator:
