Excel Formula Dilemma

I

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
857
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Please help, finding a solution is driving me NUTS. For starters my data as described.

Column A: Match Start (date started)
Column B: My Team (Always the same value)
Column C: Opposing Team (Many duplicates)
Column D: Match End (date end)
Column E: Win/Loss (value = Win or Loss depending on the result)
*The data is quite larger over 300 rows*

My end result here is I want a standings bracket (how I fair against each team (win, loss, win %). Problem I am having is the duplicates are giving me a headache, and I want it to auto pull. So not having to intervene by creating filters or auto filters. Sample below, in a smaller scale but in theory that is how my spreadsheet is made up:

[TABLE="width: 411"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]Team[/TD]
[TD]Opponent[/TD]
[TD]End Date[/TD]
[TD]Win/Loss[/TD]
[/TR]
[TR]
[TD]9/15/2016[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]9/15/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/16/2016[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]9/16/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/17/2016[/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD]9/17/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/18/2019[/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD]9/19/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/19/2016[/TD]
[TD]A[/TD]
[TD]F[/TD]
[TD]9/19/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/20/2016[/TD]
[TD]A[/TD]
[TD]G[/TD]
[TD]9/21/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/21/2016[/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD]9/22/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/22/2016[/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD]9/23/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/23/2016[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]9/24/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/24/2016[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]9/25/2016[/TD]
[TD]Win[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]
 
Sort by date Sort by votes
Re: Excel Formila Dilemma.HELP ME PLEASE

Just as a tip, this is different enough that it's probably worth creating a new thread. At some point I stop following old threads, and other people often don't notice if a new question is added to an existing thread. You are lucky in that I can figure out your question, but you're probably not getting a lot of other eyeballs on your question, and some of them might have a better idea than me (hard to believe, but possible! :biggrin:).

To your question, try:

ABCDEFGHIJ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Wins[/TD]
[TD="bgcolor: #FAFAFA"]Losses[/TD]
[TD="bgcolor: #FAFAFA"]Win %[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Wins[/TD]
[TD="bgcolor: #FAFAFA"]Losses[/TD]
[TD="bgcolor: #FAFAFA"]Win %[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Record Before Tracking[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]83.33%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Record Before Tracking[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]83.33%[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Current Record[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Current Record[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Current Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Current Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Longest Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Longest Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Start Date[/TD]
[TD="bgcolor: #FAFAFA"]Home Team[/TD]
[TD="bgcolor: #FAFAFA"]Opponent[/TD]
[TD="bgcolor: #FAFAFA"]End Date[/TD]
[TD="bgcolor: #FAFAFA"]Win/Loss[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/29/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/30/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/1/2016[/TD]
[TD="bgcolor: #FAFAFA"]Loss[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/18/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team G[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/18/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team G[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/2/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team L[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Loss[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team L[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/3/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/20/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team M[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/21/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/20/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team M[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/4/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=COUNTIFS($B$8:$B$1002,A1,$E$8:$E$1002,"Win")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=COUNTIFS($B$8:$B$1002,A1,$E$8:$E$1002,"Loss")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=B2/(B2+C2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]{=COUNTIF(INDEX($B8:$B$1008,MAX(($E$8:$E$1008="Loss")*($B$8:$B$1008=A1)*ROW($E$8:$E$1008),ROW($E$8)-1)-ROW($E$8)+2,1):$B$1008,A1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]{=COUNTIF(INDEX($B8:$B$1008,MAX(($E$8:$E$1008="Win")*($B$8:$B$1008=A1)*ROW($E$8:$E$1008),ROW($E$8)-1)-ROW($E$8)+2):$B$1008,A1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]{=MAX(FREQUENCY(IF(($E$8:$E$1002="Win")*($B$8:$B$1002=A1),ROW($E$8:$E$1002)),IF(($E$8:$E$1002<>"Win")*($B$8:$B$1002=A1),ROW($E$8:$E$1002))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=MAX(FREQUENCY(IF(($E$8:$E$1002="Loss")*($B$8:$B$1002=A1),ROW($E$8:$E$1002)),IF(($E$8:$E$1002<>"Loss")*($B$8:$B$1002=A1),ROW($E$8:$E$1002))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




For your longest streak question, all you really need to to is add a condition to the formula that checks if the line item is for the team you're interested in. Everything else works OK.

For the current streak question, I tried the same approach, but it did not work. The formula basically assumes that the team's entries are sequential. I thought of a complicated way to fix it up, but instead I basically used the array part of the formula to find the lowest row that says "Loss", and then just used COUNTIFS to count the number of entries below that which must be "Win"s.

Since I used absolute references for the data columns ($) and relative reference for the team (A1), you can copy the formulas directly from the B:D columns to H:J and they'll work.

Hope this helps!
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Re: Excel Formila Dilemma.HELP ME PLEASE

Eric W said:
Just as a tip, this is different enough that it's probably worth creating a new thread. At some point I stop following old threads, and other people often don't notice if a new question is added to an existing thread. You are lucky in that I can figure out your question, but you're probably not getting a lot of other eyeballs on your question, and some of them might have a better idea than me (hard to believe, but possible! :biggrin:).

To your question, try:

ABCDEFGHIJ

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Wins[/TD]
[TD="bgcolor: #FAFAFA"]Losses[/TD]
[TD="bgcolor: #FAFAFA"]Win %[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Wins[/TD]
[TD="bgcolor: #FAFAFA"]Losses[/TD]
[TD="bgcolor: #FAFAFA"]Win %[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Record Before Tracking[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]83.33%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Record Before Tracking[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]83.33%[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Current Record[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Current Record[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Current Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Current Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Longest Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Longest Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Start Date[/TD]
[TD="bgcolor: #FAFAFA"]Home Team[/TD]
[TD="bgcolor: #FAFAFA"]Opponent[/TD]
[TD="bgcolor: #FAFAFA"]End Date[/TD]
[TD="bgcolor: #FAFAFA"]Win/Loss[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/29/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/30/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/1/2016[/TD]
[TD="bgcolor: #FAFAFA"]Loss[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/18/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team G[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/18/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team G[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/2/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team L[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Loss[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team L[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/3/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/20/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team A[/TD]
[TD="bgcolor: #FAFAFA"]Team M[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/21/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/20/2016[/TD]
[TD="bgcolor: #FAFAFA"]Team B[/TD]
[TD="bgcolor: #FAFAFA"]Team M[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/4/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=COUNTIFS($B$8:$B$1002,A1,$E$8:$E$1002,"Win")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=COUNTIFS($B$8:$B$1002,A1,$E$8:$E$1002,"Loss")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=B2/(B2+C2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]{=COUNTIF(INDEX($B8:$B$1008,MAX(($E$8:$E$1008="Loss")*($B$8:$B$1008=A1)*ROW($E$8:$E$1008),ROW($E$8)-1)-ROW($E$8)+2,1):$B$1008,A1)}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]{=COUNTIF(INDEX($B8:$B$1008,MAX(($E$8:$E$1008="Win")*($B$8:$B$1008=A1)*ROW($E$8:$E$1008),ROW($E$8)-1)-ROW($E$8)+2):$B$1008,A1)}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]{=MAX(FREQUENCY(IF(($E$8:$E$1002="Win")*($B$8:$B$1002=A1),ROW($E$8:$E$1002)),IF(($E$8:$E$1002<>"Win")*($B$8:$B$1002=A1),ROW($E$8:$E$1002))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=MAX(FREQUENCY(IF(($E$8:$E$1002="Loss")*($B$8:$B$1002=A1),ROW($E$8:$E$1002)),IF(($E$8:$E$1002<>"Loss")*($B$8:$B$1002=A1),ROW($E$8:$E$1002))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




For your longest streak question, all you really need to to is add a condition to the formula that checks if the line item is for the team you're interested in. Everything else works OK.

For the current streak question, I tried the same approach, but it did not work. The formula basically assumes that the team's entries are sequential. I thought of a complicated way to fix it up, but instead I basically used the array part of the formula to find the lowest row that says "Loss", and then just used COUNTIFS to count the number of entries below that which must be "Win"s.

Since I used absolute references for the data columns ($) and relative reference for the team (A1), you can copy the formulas directly from the B:D columns to H:J and they'll work.

Hope this helps!
Click to expand...

But then who would be putting you to work? :laugh: Plus rather give you the business haha! By rightfully so it is noted for next time good sir :)

Okay so tested it out all looks gravy except one minor thing. If the cell value is blank (Win/Loss column) it adds to to both a Win and a Loss - For consecutive win/losses ONLY. So often times a result wont be known until the next day, anyway we can have it not include that blank cell value?
 
Upvote 0
Re: Excel Formila Dilemma.HELP ME PLEASE

I think you're just trying to stump me now!

Try:

ABC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Current Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=SUMPRODUCT(--(ROW($B$8:$B$1008)>MAX(($E$8:$E$1008="Loss")*($B$8:$B$1008=A1)*ROW($E$8:$E$1008))),--($B$8:$B$1008=A1),--($E$8:$E$1008<>""))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=SUMPRODUCT(--(ROW($B$8:$B$1008)>MAX(($E$8:$E$1008="Win")*($B$8:$B$1008=A1)*ROW($E$8:$E$1008))),--($B$8:$B$1008=A1),--($E$8:$E$1008<>""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Solution
Re: Excel Formila Dilemma.HELP ME PLEASE

Eric W said:
I think you're just trying to stump me now!

Try:

ABC

<tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Current Streak[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=SUMPRODUCT(--(ROW($B$8:$B$1008)>MAX(($E$8:$E$1008="Loss")*($B$8:$B$1008=A1)*ROW($E$8:$E$1008))),--($B$8:$B$1008=A1),--($E$8:$E$1008<>""))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=SUMPRODUCT(--(ROW($B$8:$B$1008)>MAX(($E$8:$E$1008="Win")*($B$8:$B$1008=A1)*ROW($E$8:$E$1008))),--($B$8:$B$1008=A1),--($E$8:$E$1008<>""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
There you go! See i can't stump you :eeek: once again you have saved me from aggravation!! Do you have a download function on you so I may upload this knowledge to myself :stickouttounge:
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Need formula for populating Date Status
Replies
7
Views
44
Joe4
Joe4
B
  • Solved
Rank winning percentage for sport game
Replies
9
Views
2K
Peter_SSs
Peter_SSs
L
  • Question Question
Formula question; multiple lookups?
Replies
1
Views
431
bobaol
B
L
  • Question Question
Sending Email with Table in Body
Replies
3
Views
620
Domenic
D
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
85
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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