formula to measure both teams to score win and loss streaks

J

jim_water

New Member
Joined
Aug 29, 2018
Messages
7
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Upvote 0
Eric W said:
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
Click to expand...



Hi eric,
how do you mean data organzied?
 
Upvote 0
What does it look like in your workbook? Look at post 6 from the 2nd link above for an example.
 
Upvote 0
Eric W said:
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
Click to expand...
 
Upvote 0
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Home team[/TD]
[TD]away team[/TD]
[TD]Fulltime home goals[/TD]
[TD]Fulltime away goals[/TD]
[TD]fulltime result[/TD]
[TD]both teams to score win streak[/TD]
[TD]both teams to score loss streak[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jim united[/TD]
[TD]joe city[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]H[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]D[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]Home team[/TD]
[TD]Away team[/TD]
[TD]FTHG[/TD]
[TD]FTAG[/TD]
[TD]FTR[/TD]
[TD]Both teams to score win streak[/TD]
[TD]Both teams to score loss streak[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]liverpool[/TD]
[TD]man united[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]H[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]arsenal[/TD]
[TD]chelsea[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]D[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aston villa[/TD]
[TD]manchester city[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]H[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Much better example!

Try this:

ABCDEFG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Home team[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Away team[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]FTHG[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]FTAG[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]FTR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Both teams to score win streak[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Both teams to score loss streak[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]liverpool[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]man united[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]H[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]arsenal[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]chelsea[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]aston villa[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]manchester city[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]H[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(PRODUCT(C2:D2),SUM(F1,1),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(PRODUCT(C2:D2)=0,SUM(G1,1),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]Both teams to score
win streak[/TD]
[TD]Both teams to score
loss streak[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
Thanks for the post.
The first one works for F2, but the one for G2 just shows 1's, like so..
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Establishing a streak of non-numerical values.
Replies
8
Views
410
HockeyBoi
H
S
  • Solved
Counting streak in a table column
Replies
3
Views
793
Eric W
Eric W
M
  • Question Question
Winning streaks
Replies
2
Views
282
etaf
etaf
H
  • Question Question
Winning Streak
Replies
3
Views
226
Phuoc
P
B
  • Solved
Rank winning percentage for sport game
Replies
9
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top