Hi,
I had been trying to figure out calculating streaks (win/loss) for my football's team statistics and is facing the following 2 issues.
My example:
Column: A B C D E F G H I J K L M N O P Q R
Row 1: w w w L 0 L 0 L 0 w w w 0 0 w L 0 L
0 denotes non-appearance and thus should not be considered in the calculation.
1) To find the current streak. Desired Result - 2L
I had been using the below formula to find the current streak but the formula takes zero into consideration and gives me the result as 1L. How can i make the forumla to ignore the zeros in the array?
=LOOKUP(1E+100,FREQUENCY(IF(A1:R1=LOOKUP("zzz",A1:R1),COLUMN(A1:R1)),IF(A1:R1<>LOOKUP("zzz",A1:R1),COLUMN(A1:R1))))&""&LOOKUP("zzz",A1:R1)
2) To find the longest winning or losing streak. Desired result: 4w and 3L
Similarly, I had been using the below formula to find the longest winning and losing streak but the formula takes zero into consideration and give me the result as 3w and 1L.
How can i make the forumla to ignore the zeros in the array?
Longest Winning Streak:
=MAX(FREQUENCY(IF(A1:R1="W",COLUMN(A1:R1)),IF(A1:R1<>"W",COLUMN(A1:R1))))
Longest Losing Streak:
=MAX(FREQUENCY(IF(A1:R1="l",COLUMN(A1:R1)),IF(A1:R1<>"l",COLUMN(A1:R1))))
Thank you so much!
Best Regards,
Poko.
Bookmarks