Calculating current streaks with a certain criteria

speedychaz94 · Dec 7, 2020

Hello to whoever is reading this.

I have joined and became a member. I have never asked a question on a forum before as I am usually able to find things myself. However, this time I am really struggling so I have concluded that I need help. I am no expert at excel but I have been learning quite a lot over the past month and I have been enjoying it.
So....my question.
I have created a large spreadsheet with over 2000 entries on UFC fighters (I am a bit of a UFC nerd). On my sheet I have the outcomes of each athlete's fights. I display their results on a scoring criteria (3=win via stoppage, 2=win via decision, 1=draw, 0=No contest & -1=loss). I have already calculated the main stats (e.g. no of wins/losses/draws) but I am struggling to calculate current streak of wins via stoppage (current finish streak or 3's). I assume the formula will include INDEX & MATCH but I am not 100% sure how to put it together. I would really appreciate some help. I have attached a sample of my spreadsheet. Or for those that can't access it....

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Fighter Name	1st Fight	2nd Fight	3rd Fight	4th Fight	5th Fight	6th Fight	7th Fight	8th Fight	9th Fight	10th Fight	No of App	Wins	Finishes	Losses/NC	Draws	Points	Highest Unbeaten/S	Current UnB/S	Highest Finish/S	Current F/S
2	Jimmy	2	2	-1	0	3	2	3				7	5	2	2	0	11	3	4	1	?
3	Bob	-1	-1	2	2	2	3					6	4	1	2	0	7	4	4	1	?
4	Gary	2	3	3	3	2	2	-1	3			8	7	4	1	0	17	7	1	3	?
5	Ryan	3	3	1	2	2	3	3				7	6	4	0	1	17	4	7	2	?
6	Lewis	3	-1	-1	-1	-1						5	1	1	4	0	-1	2	0	1	?
7	Randy	-1	2	2	1	3	3	3	1	-1		9	5	3	2	2	13	3	0	3	?
8	Joe	3	3	-1	-1	2	2	3				7	5	3	2	0	11	3	3	2	?
9	Doug	3	3	1	2	3	2	3	-1	2		9	7	4	1	1	18	4	1	2	?
10	Frank	3	-1	-1	2	3	0	1	3	3	3	10	6	5	3	1	16	3	7	3	?

So again, I need to calculate the current streak of 3's (wins via stoppage).

The results in column U2, for current finish streak should be U2=1, U3=1, U4=1, U5=2, U6=0, U7=0, U8=1, U9=0, U10=3.

*Remember not every fighter has the same number of fights which I believe makes this formula a bit more complicated.

Thanks in advanced.

lrobbo314 · Dec 7, 2020

How about this?

Book2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Fighter Name

1st Fight

2nd Fight

3rd Fight

4th Fight

5th Fight

6th Fight

7th Fight

8th Fight

9th Fight

10th Fight

No of App

Wins

Finishes

Losses/NC

Draws

Points

Highest Unbeaten/S

Current UnB/S

Highest Finish/S

Current F/S

2

Jimmy

2

-1

0

3

2

3

7

5

2

0

11

3

4

1

3

Bob

-1

2

3

6

4

1

2

0

7

4

1

4

Gary

2

3

2

-1

3

8

7

4

1

0

17

7

1

3

1

5

Ryan

3

1

2

3

7

6

4

0

1

17

4

7

2

6

Lewis

3

-1

5

1

4

0

-1

2

0

1

0

7

Randy

-1

2

1

3

1

-1

9

5

3

2

13

3

0

3

0

8

Joe

3

-1

2

3

7

5

3

2

0

11

3

2

1

9

Doug

3

1

2

3

2

3

-1

2

9

7

4

1

18

4

1

2

0

10

Frank

3

-1

2

3

0

1

3

10

6

5

3

1

16

3

7

3

Sheet3

Cell Formulas
Range	Formula
U2:U10	U2	=COUNTIF(INDIRECT(ADDRESS(ROW(),MATCH(2,1/(B2:K2<3),1)+2)&":"&"K"&ROW()),3)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

lrobbo314 · Dec 7, 2020

A way using VBA or Power Query too.

Book2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

V

W

1

Fighter Name

1st Fight

2nd Fight

3rd Fight

4th Fight

5th Fight

6th Fight

7th Fight

8th Fight

9th Fight

10th Fight

No of App

Wins

Finishes

Losses/NC

Draws

Points

Highest Unbeaten/S

Current UnB/S

Highest Finish/S

VBA WIN

Current F/S

2

Jimmy

2

-1

0

3

2

3

7

5

2

0

11

3

4

1

3

Bob

-1

2

3

6

4

1

2

0

7

4

1

4

Gary

2

3

2

-1

3

8

7

4

1

0

17

7

1

3

1

5

Ryan

3

1

2

3

7

6

4

0

1

17

4

7

2

6

Lewis

3

-1

5

1

4

0

-1

2

0

1

0

7

Randy

-1

2

1

3

1

-1

9

5

3

2

13

3

0

3

0

8

Joe

3

-1

2

3

7

5

3

2

0

11

3

2

1

9

Doug

3

1

2

3

2

3

-1

2

9

7

4

1

18

4

1

2

0

10

Frank

3

-1

2

3

0

1

3

10

6

5

3

1

16

3

7

3

Sheet3

Cell Formulas
Range	Formula
V2:V10	V2	=WINS(B2:K2)

VBA Code:

Function WINS(r As Range) As Integer
Dim AR() As Variant: AR = r.Value2

For i = UBound(AR, 2) To LBound(AR) Step -1
    If AR(1, i) <> "" Then
        If AR(1, i) = 3 Then WINS = WINS + 1 Else Exit For
    End If
Next i
End Function

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 0, 1),
    RC = Table.SelectColumns(Index,{"1st Fight", "2nd Fight", "3rd Fight", "4th Fight", "5th Fight", "6th Fight", "7th Fight", "8th Fight", "9th Fight", "10th Fight", "Index"}),
    Unpivot = Table.UnpivotOtherColumns(RC, {"Index"}, "Attribute", "Value"),
    NoBlank = Table.SelectRows(Unpivot, each ([Value] <> "")),
    X = Table.TransformColumns(NoBlank,{{"Value", each if _ = 3 then "X" else "N"}}),
    Group0 = Table.Group(X, {"Index", "Value"}, {{"Count", each Table.AddIndexColumn(_, "subsetIndex",1,1), type table}},GroupKind.Local),
    Group1 = Table.Group(Group0, {"Index"}, {{"Count", each _, type table}}),
    Last = Table.TransformColumns(Group1,{{"Count", each Table.Last(_)}}),
    Expand = Table.ExpandRecordColumn(Last, "Count", {"Value", "Count"}, {"Value", "Count.1"}),
    Current = Table.AddColumn(Expand, "Current F/S", each if [Value]= "N" then 0 else Table.RowCount([Count.1])),
    SC = Table.SelectColumns(Current,{"Current F/S"})
in
    SC

Eric W · Dec 7, 2020

Another option:

U2:

Excel Formula:

=MAX(IFERROR(AGGREGATE(14,6,COLUMN(B2:K2)/(B2:K2=3),1),0)-AGGREGATE(14,6,COLUMN(A2:K2)/(A2:K2<>3)/(A2:K2<>""),1),0)

speedychaz94 · Dec 12, 2020

lrobbo314 said:
How about this?

Book2
A B C D E F G H I J K L M N O P Q R S T U
1 Fighter Name 1st Fight 2nd Fight 3rd Fight 4th Fight 5th Fight 6th Fight 7th Fight 8th Fight 9th Fight 10th Fight No of App Wins Finishes Losses/NC Draws Points Highest Unbeaten/S Current UnB/S Highest Finish/S Current F/S
2 Jimmy 2 2 -1 0 3 2 3 7 5 2 2 0 11 3 4 1 1
3 Bob -1 -1 2 2 2 3 6 4 1 2 0 7 4 4 1 1
4 Gary 2 3 3 3 2 2 -1 3 8 7 4 1 0 17 7 1 3 1
5 Ryan 3 3 1 2 2 3 3 7 6 4 0 1 17 4 7 2 2
6 Lewis 3 -1 -1 -1 -1 5 1 1 4 0 -1 2 0 1 0
7 Randy -1 2 2 1 3 3 3 1 -1 9 5 3 2 2 13 3 0 3 0
8 Joe 3 3 -1 -1 2 2 3 7 5 3 2 0 11 3 3 2 1
9 Doug 3 3 1 2 3 2 3 -1 2 9 7 4 1 1 18 4 1 2 0
10 Frank 3 -1 -1 2 3 0 1 3 3 3 10 6 5 3 1 16 3 7 3 3
Sheet3
Cell Formulas
Range Formula
U2:U10 U2 =COUNTIF(INDIRECT(ADDRESS(ROW(),MATCH(2,1/(B2:K2<3),1)+2)&":"&"K"&ROW()),3)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Thanks for your help.

speedychaz94 · Dec 12, 2020

Eric W said:
Another option:

U2:

Excel Formula:

=MAX(IFERROR(AGGREGATE(14,6,COLUMN(B2:K2)/(B2:K2=3),1),0)-AGGREGATE(14,6,COLUMN(A2:K2)/(A2:K2<>3)/(A2:K2<>""),1),0)

Thanks for your help. I am still learning.

Calculating current streaks with a certain criteria

speedychaz94

New Member

Excel Facts

lrobbo314

Well-known Member

lrobbo314

Well-known Member

Eric W

MrExcel MVP

speedychaz94

New Member

speedychaz94

New Member

Similar threads

Forum statistics

Share this page

Calculating current streaks with a certain criteria

speedychaz94

New Member

Excel Facts

lrobbo314

Well-known Member

lrobbo314

Well-known Member

Eric W

MrExcel MVP

speedychaz94

New Member

speedychaz94

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock