formula to measure both teams to score win and loss streaks

[jim_water]

Hi,
I am trying to find the win and loss streaks for set of games to find out whether both teams have scored in match and if so carry it on as a streak.
Thank you

 

[Eric W]

Welcome to the forum.

It's really hard to come up with something when we can't see how you have your data organized. Try to print a sample, possibly using one of the screen printer tools (see HTML Maker in my signature).

In the meantime, these links might give you some ideas:

https://www.mrexcel.com/forum/excel-questions/997369-excel-formula-dilemma.html


https://www.mrexcel.com/forum/excel-questions/978269-streaks-current-streak-conditions.html

 

[jim_water]

Welcome to the forum.

It's really hard to come up with something when we can't see how you have your data organized. Try to print a sample, possibly using one of the screen printer tools (see HTML Maker in my signature).

In the meantime, these links might give you some ideas:

https://www.mrexcel.com/forum/excel-questions/997369-excel-formula-dilemma.html


https://www.mrexcel.com/forum/excel-questions/978269-streaks-current-streak-conditions.html

Hi eric,
how do you mean data organzied?

 

[Eric W]

What does it look like in your workbook? Look at post 6 from the 2nd link above for an example.

 

[jim_water]

What does it look like in your workbook? Look at post 6 from the 2nd link above for an example.[/QUOTE

Column 1 has home team
Column 2 has away team
Column 3 has home goals
Column 4 has away goals
Column 5 has result
Column 6 both teams to score win streak
Column 7 both teams to score loss streak


Appreciate you’re efforts to help a noob, hope this is more help

 

[jim_water]

HomeTeam AwayTeam FTHG FTAG

FTR Btts win streak Btts lose streak
0 1

 

[jim_water]

Home team	away team	Fulltime home goals	Fulltime away goals	fulltime result	both teams to score win streak	both teams to score loss streak
jim united	joe city	1	3	A	1	0
		1	0	H	0	1
		0	0	D	0	2

<tbody>
</tbody>

 

[jim_water]

Home team	Away team	FTHG	FTAG	FTR	Both teams to score win streak	Both teams to score loss streak
liverpool	man united	1	0	H	0	1
arsenal	chelsea	1	1	D	1	0
aston villa	manchester city	2	1	H	2	0

<tbody>
</tbody>

 

[Eric W]

Much better example!

Try this:

	A	B	C	D	E	F	G
1	Home team	Away team	FTHG	FTAG	FTR	Both teams to score win streak	Both teams to score loss streak
2	liverpool	man united	1	0	H	0	1
3	arsenal	chelsea	1	1	D	1	0
4	aston villa	manchester city	2	1	H	2	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =IF(PRODUCT(C2:D2),SUM(F1,1),0) G2 =IF(PRODUCT(C2:D2)=0,SUM(G1,1),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I'll be away for a few days, but I'll check this when I get back, or someone else may chime in if you have more questions.

 

[jim_water]

Both teams to score win streak	Both teams to score loss streak
0	1
1	1
2	1

<tbody>
</tbody>

Thanks for the post.
The first one works for F2, but the one for G2 just shows 1's, like so..