Hello all
How can i calculate the longest streak and the current streak of specific soccer results (ex: 1-1) if goals are placed in two columns.
Ex 1:
A B
2 0
1 1
1 1
1 1
1 1
1 1
2 3
2 1
1 1
1 1
results:
Longest 1-1 streak: 5
Current 1-1 streak: 2
Ex 2:
A B
2 0
1 1
1 1
1 1
1 1
1 1
2 3
2 1
1 1
1 1
2 2
results:
Longest 1-1 streak: 5
Current 1-1 streak: 0 this is because the actual result (2-2) is <>1-1, so the last streak of 1-1 is ignored.
Thanks
With your data in the range A2:B11...
For the longest streak of 1-1...
Array entered**:
=MAX(FREQUENCY(IF((A2:A11=1)*(B2:B11=1),ROW(A2:A11)),IF(A2:A11<>1,ROW(A2:A11))))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Not sure I understand the current streak requirement.
Last edited by Tony Valko; 10-05-2016 at 11:42 AM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Ok, I think I figured it out.
With your data in the range A2:B11...
D2 = 1
E2 = 1
For the longest streak of 1-1...
Array entered**:
=MAX(FREQUENCY(IF((A2:A11=D2)*(B2:B11=E2),ROW(A2:A11)),IF(A2:A11<>D2,ROW(A2:A11))))
For the current streak...
Array entered**:
=IF(OR(LOOKUP(1000,A2:A11)<>D2,LOOKUP(1000,B2:B11)<>E2),0,LOOKUP(1000,FREQUENCY(IF((A2:A11=D2)*(B2:B11=E2),ROW(A2:A11)),IF((A2:A11<>D2)*(B2:B11<>E2),ROW(A2:A11)))))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Hi and above all thanks for your quick answer Tony Valko!
I know you are a expert in streaks because i saw many responses from you about this, and very good answers btw.
First formulas work perfect, the current streak formulas (both) works ok if all cells have data in all the range.
Per instance I am using the A2:B11 range, if last cells are blanks the current streak formula gives me zero values.
Forgive me if i ask, there is any way to calculate without using the helper cells?.
edit:
sorry for the silly question of the helper cells, i just changed the value in the formula for the desired result an problem solved.
I am stuck on the longest streak for 0-0 and other results.
Last edited by Lehoi; 10-05-2016 at 01:40 PM.
I made a few test and the longest streak formula works ok for tie results, ej; 1-1, 2-2, etc, but for the 0-0 result the blanks cells are considered as 0 too
If i want to adapt the formula for other results than ties like 0-1, 2-3, etc, how can i do that? i can change the values in the first part of the formula, but in the second part there is only one value i can change.
Sorry for so many questions
It sounds like you should use dynamic ranges to avoid the empty cells.
Like:
Range1
Refers to: =$A$2:INDEX($A:$A,MATCH(1000,$A:$A))
Range2
Refers to: =$B$2:INDEX($B:$B,MATCH(1000,$B:$B))
Here's a sample file with this implemented.
Interesting and very effective solution!, works perfect.
Thank you!!!
You're welcome. Thanks for the feedback!
Lehoi,
You may be interested in taking a look at this small version of a league table that incorporates formulas for past 6 results, next 6 fixtures, winning streaks and losing streaks.
The workbook has been butchered to remove Fantasy league players & scores btw so some aspects of the workbook may seem a bit OTT.
A spreadsheet very impressive with a lot of things that i will use it to improve mine (very modest spreadsheet compared to yours).Originally Posted by BlindAlley
Thank you very much for your help BlindAlley!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks