Excel Formula Dilemma

[ItalianPlatinum]

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]

 

[philaugust2004]

Try this
=DATEDIF($A$2,A2,"d")/COUNTIF($C$2:C2,C2)

You will need to format the column as general. I don't know why it turns it into date when datedif returns a number in its own

 

[ItalianPlatinum]

Re: Excel Formila Dilemma.HELP ME PLEASE

Welcome to the forum.

Maybe:

	A	B	C	D	E	F	G	H	I
B
C
D
E
F
G

<tbody>
[TD="align: center"]1
[/TD]
[TD="bgcolor: #FAFAFA"]Start Date
[/TD]
[TD="bgcolor: #FAFAFA"]Team
[/TD]
[TD="bgcolor: #FAFAFA"]Opponent
[/TD]
[TD="bgcolor: #FAFAFA"]End Date
[/TD]
[TD="bgcolor: #FAFAFA"]Win/Loss
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Opponent
[/TD]
[TD="bgcolor: #FAFAFA"]Wins
[/TD]
[TD="bgcolor: #FAFAFA"]Losses
[/TD]

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

[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016
[/TD]
[TD="bgcolor: #FAFAFA"]A
[/TD]
[TD="bgcolor: #FAFAFA"]C
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016
[/TD]
[TD="bgcolor: #FAFAFA"]Win
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016
[/TD]
[TD="bgcolor: #FAFAFA"]A
[/TD]
[TD="bgcolor: #FAFAFA"]D
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016
[/TD]
[TD="bgcolor: #FAFAFA"]Win
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/18/2019
[/TD]
[TD="bgcolor: #FAFAFA"]A
[/TD]
[TD="bgcolor: #FAFAFA"]E
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016
[/TD]
[TD="bgcolor: #FAFAFA"]Win
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016
[/TD]
[TD="bgcolor: #FAFAFA"]A
[/TD]
[TD="bgcolor: #FAFAFA"]F
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016
[/TD]
[TD="bgcolor: #FAFAFA"]Win
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]9
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/22/2016
[/TD]
[TD="bgcolor: #FAFAFA"]A
[/TD]
[TD="bgcolor: #FAFAFA"]E
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/23/2016
[/TD]
[TD="bgcolor: #FAFAFA"]Win
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/23/2016
[/TD]
[TD="bgcolor: #FAFAFA"]A
[/TD]
[TD="bgcolor: #FAFAFA"]B
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/24/2016
[/TD]
[TD="bgcolor: #FAFAFA"]Win
[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]11
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/24/2016
[/TD]
[TD="bgcolor: #FAFAFA"]A
[/TD]
[TD="bgcolor: #FAFAFA"]C
[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/25/2016
[/TD]
[TD="bgcolor: #FAFAFA"]Win
[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]12
[/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: center"]13
[/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>

Sheet13​

[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"]H2
[/TH]
[TD="align: left"]=IF(G2="","",COUNTIFS($C$2:$C$100,G2,$E$2:$E$100,"Win"))
[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I2
[/TH]
[TD="align: left"]=IF(G2="","",COUNTIFS($C$2:$C$100,G2,$E$2:$E$100,"Loss"))
[/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"]G2
[/TH]
[TD="align: left"]{=IF(G1="","",IFERROR(INDEX($C$2:$C$100,SMALL(IF($C$2:$C$100<>"",IF(COUNTIF($G$1:$G1,$C$2:$C$100)=0,ROW($C$2:$C$100)-ROW($C$2)+1)),1)),""))}
[/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]

Formulas worked great, with one strange hiccup. When I try to sort the column by Matches played (Column J = Column H + Column I) it doesn't sort?

 

[Eric W]

Re: Excel Formila Dilemma.HELP ME PLEASE

That's just an odd property of this type of formula. The formulas do sort, but when they get in the new spots, they recalculate and regenerate the same list they originally did. If you want to sort it, you'll need to copy those columns, and Paste as Values somewhere else, then sort the values.

Depending on what sort order you actually want, I may be able to adapt the formula to generate the list in that order. But if you want to sort in multiple ways, you're better off with the Paste Values method.

 

[ItalianPlatinum]

Re: Excel Formila Dilemma.HELP ME PLEASE

That's just an odd property of this type of formula. The formulas do sort, but when they get in the new spots, they recalculate and regenerate the same list they originally did. If you want to sort it, you'll need to copy those columns, and Paste as Values somewhere else, then sort the values.

Depending on what sort order you actually want, I may be able to adapt the formula to generate the list in that order. But if you want to sort in multiple ways, you're better off with the Paste Values method.

True it would be good to be able to sort among the columns. I guess I really only care about Most matches (largest to smallest).

 

[Eric W]

Re: Excel Formila Dilemma.HELP ME PLEASE

Sorry about the delay. I created the updated formulas last week, but somehow I didn't get them uploaded to the forum.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
B
C
D
E
F
G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Start Date[/TD]
[TD="bgcolor: #FAFAFA"]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"]Opponent[/TD]
[TD="bgcolor: #FAFAFA"]Wins[/TD]
[TD="bgcolor: #FAFAFA"]Losses[/TD]
[TD="bgcolor: #FAFAFA"]Total games[/TD]
[TD="bgcolor: #FAFAFA"]Percent[/TD]
[TD="bgcolor: #FAFAFA"]Avg. Days between games[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Unique list[/TD]
[TD="bgcolor: #FAFAFA"]Total games[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]1.3333333[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/18/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/20/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]G[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/21/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]G[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/21/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/22/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

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

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/22/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/23/2016[/TD]
[TD="bgcolor: #FAFAFA"]Loss[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

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

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/23/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/24/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

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

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/24/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/25/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

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

[TD="align: center"]12[/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="bgcolor: #FAFAFA"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[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"]H2[/TH]
[TD="align: left"]=IF(J2="","",COUNTIFS(C:C,G2,E:E,"Win"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(J2="","",COUNTIFS(C:C,G2,E:E,"Loss"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=IF(N2="","",LARGE($O$2:$O$100,ROWS($J$2:$J2)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=IF(J2="","",H2/(H2+I2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]O2[/TH]
[TD="align: left"]=IF(N2="","",COUNTIF(C:C,N2))[/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"]G2[/TH]
[TD="align: left"]{=IF(J2="","",INDEX($N:$N,SMALL(IF($O$2:$O$100=J2,ROW($O$2:$O$100)),COUNTIF($J$2:$J2,J2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L2[/TH]
[TD="align: left"]{=IF(J2="","",(MAX(IF($C$2:$C$100=G2,$A$2:$A$100))-MIN(IF($C$2:$C$100=G2,$A$2:$A$100)))/J2)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]{=IF(N1="","",IFERROR(INDEX($C$2:$C$100,SMALL(IF($C$2:$C$100<>"",IF(COUNTIF($N$1:$N1,$C$2:$C$100)=0,ROW($C$2:$C$100)-ROW($C$2)+1)),1)),""))}[/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]



The formulas in N:O are there just to create a list of unique opponents and the number of games.

The formulas in J and G are there to automatically sort that list by number of games.

The H, I, K, L formulas are just statistics functions using the other columns.

Hope this helps.

 

[ItalianPlatinum]

Re: Excel Formila Dilemma.HELP ME PLEASE

Sorry about the delay. I created the updated formulas last week, but somehow I didn't get them uploaded to the forum.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
B
C
D
E
F
G

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Start Date[/TD]
[TD="bgcolor: #FAFAFA"]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"]Opponent[/TD]
[TD="bgcolor: #FAFAFA"]Wins[/TD]
[TD="bgcolor: #FAFAFA"]Losses[/TD]
[TD="bgcolor: #FAFAFA"]Total games[/TD]
[TD="bgcolor: #FAFAFA"]Percent[/TD]
[TD="bgcolor: #FAFAFA"]Avg. Days between games[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Unique list[/TD]
[TD="bgcolor: #FAFAFA"]Total games[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]1.3333333[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/18/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/20/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]G[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/21/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]G[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/21/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/22/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

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

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/22/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/23/2016[/TD]
[TD="bgcolor: #FAFAFA"]Loss[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

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

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/23/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/24/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

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

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/24/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/25/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

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

[TD="align: center"]12[/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="bgcolor: #FAFAFA"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[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"]H2[/TH]
[TD="align: left"]=IF(J2="","",COUNTIFS(C:C,G2,E:E,"Win"))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(J2="","",COUNTIFS(C:C,G2,E:E,"Loss"))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=IF(N2="","",LARGE($O$2:$O$100,ROWS($J$2:$J2)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=IF(J2="","",H2/(H2+I2))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]O2[/TH]
[TD="align: left"]=IF(N2="","",COUNTIF(C:C,N2))[/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"]G2[/TH]
[TD="align: left"]{=IF(J2="","",INDEX($N:$N,SMALL(IF($O$2:$O$100=J2,ROW($O$2:$O$100)),COUNTIF($J$2:$J2,J2))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]L2[/TH]
[TD="align: left"]{=IF(J2="","",(MAX(IF($C$2:$C$100=G2,$A$2:$A$100))-MIN(IF($C$2:$C$100=G2,$A$2:$A$100)))/J2)}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]{=IF(N1="","",IFERROR(INDEX($C$2:$C$100,SMALL(IF($C$2:$C$100<>"",IF(COUNTIF($N$1:$N1,$C$2:$C$100)=0,ROW($C$2:$C$100)-ROW($C$2)+1)),1)),""))}[/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]



The formulas in N:O are there just to create a list of unique opponents and the number of games.

The formulas in J and G are there to automatically sort that list by number of games.

The H, I, K, L formulas are just statistics functions using the other columns.

Hope this helps.

Everything works like a GEM! Thank you so much, you have saved me so much time and I have created so much wasted time/work for you

 

[Eric W]

Re: Excel Formila Dilemma.HELP ME PLEASE

Believe me, I already waste too much time!

I'm just glad this time I was able to help someone else.

 

[ItalianPlatinum]

Re: Excel Formila Dilemma.HELP ME PLEASE

Believe me, I already waste too much time!

I'm just glad this time I was able to help someone else.

Those array formulas look pretty fancy, do you have some material or links I could read up on to teach myself? I am quite beginner on those.

 

[Eric W]

Re: Excel Formila Dilemma.HELP ME PLEASE

I'm largely self-taught with array formulas. In looking at examples here, I see something interesting and I'll deconstruct it to see how it works. Most of the helpers here will explain how their formulas work if you ask. (Which I'll do for any of the formulas above if you want.)

There are books devoted to array formulas, but not having read them, I can't really recommend anything. Try Googling "Excel Array formulas" and there are some very good links. Some people really recommend YouTube videos, such as:

http://www.youtube.com/user/ExcelIsFun

There's a whole section there (about halfway down) devoted to array formulas.

Good luck!

 

[ItalianPlatinum]

Re: Excel Formila Dilemma.HELP ME PLEASE

I'm largely self-taught with array formulas. In looking at examples here, I see something interesting and I'll deconstruct it to see how it works. Most of the helpers here will explain how their formulas work if you ask. (Which I'll do for any of the formulas above if you want.)

There are books devoted to array formulas, but not having read them, I can't really recommend anything. Try Googling "Excel Array formulas" and there are some very good links. Some people really recommend YouTube videos, such as:

http://www.youtube.com/user/ExcelIsFun

There's a whole section there (about halfway down) devoted to array formulas.

Good luck!

Okay so I wrestled with some array formulas and stumbled on one to give me streaks (current win/loss streak and longest win/lost streak). Well I am posting on here because I want to take them a little bit further but yeah I am stumbling now. In short I have two teams and want the array to trigger depending on what is the home team (my Team). See below for example. Basically get them in one grid and not have them separated like they currently are into two separate ones.

[TABLE="width: 1110"]
<tbody>[TR]
[TD]Team A[/TD]
[TD]Wins[/TD]
[TD]Losses[/TD]
[TD]Win %[/TD]
[TD][/TD]
[TD][/TD]
[TD]Team B[/TD]
[TD]Wins[/TD]
[TD]Losses[/TD]
[TD]Win %[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record Before Tracking[/TD]
[TD]256[/TD]
[TD]61[/TD]
[TD]80.76%[/TD]
[TD][/TD]
[TD][/TD]
[TD]Record Before Tracking[/TD]
[TD]86[/TD]
[TD]56[/TD]
[TD]60.56%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current Record[/TD]
[TD]261[/TD]
[TD]62[/TD]
[TD]80.80%[/TD]
[TD][/TD]
[TD][/TD]
[TD]Current Record[/TD]
[TD]91[/TD]
[TD]57[/TD]
[TD]61.49%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current Streak[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Current Streak[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Longest Streak[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Longest Streak[/TD]
[TD]3[/TD]
[TD]1[/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]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]Home Team
[/TD]
[TD]Opponent[/TD]
[TD]End Date[/TD]
[TD]Win/Loss[/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD]Home Team
[/TD]
[TD]Opponent[/TD]
[TD]End Date[/TD]
[TD]Win/Loss[/TD]
[/TR]
[TR]
[TD]9/15/2016[/TD]
[TD]Team A[/TD]
[TD]Team D[/TD]
[TD]9/15/2016[/TD]
[TD]Win[/TD]
[TD][/TD]
[TD]9/15/2016[/TD]
[TD]Team B[/TD]
[TD]Team D[/TD]
[TD]9/29/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/16/2016[/TD]
[TD]Team A[/TD]
[TD]Team E[/TD]
[TD]9/16/2016[/TD]
[TD]Win[/TD]
[TD][/TD]
[TD]9/16/2016[/TD]
[TD]Team B[/TD]
[TD]Team E[/TD]
[TD]9/30/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/17/2016[/TD]
[TD]Team A[/TD]
[TD]Team F[/TD]
[TD]9/17/2016[/TD]
[TD]Win[/TD]
[TD][/TD]
[TD]9/17/2016[/TD]
[TD]Team B[/TD]
[TD]Team F[/TD]
[TD]10/1/2016[/TD]
[TD]Loss[/TD]
[/TR]
[TR]
[TD]9/18/2016[/TD]
[TD]Team A[/TD]
[TD]Team G[/TD]
[TD]9/19/2016[/TD]
[TD]Win[/TD]
[TD][/TD]
[TD]9/18/2016[/TD]
[TD]Team B[/TD]
[TD]Team G[/TD]
[TD]10/2/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/19/2016[/TD]
[TD]Team A[/TD]
[TD]Team L[/TD]
[TD]9/19/2016[/TD]
[TD]Loss[/TD]
[TD][/TD]
[TD]9/19/2016[/TD]
[TD]Team B[/TD]
[TD]Team L[/TD]
[TD]10/3/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/20/2016[/TD]
[TD]Team A[/TD]
[TD]Team M[/TD]
[TD]9/21/2016[/TD]
[TD]Win[/TD]
[TD][/TD]
[TD]9/20/2016[/TD]
[TD]Team B[/TD]
[TD]Team M[/TD]
[TD]10/4/2016[/TD]
[TD]Win[/TD]
[/TR]
</tbody>[/TABLE]

Current win/loss steak formula = MAX(0,MAX((E8:E1002="Win")*ROW(E8:E1002))-MAX((E8:E1002="Loss")*ROW(E8:E1002)))

Longest win/loss streak formula = MAX(FREQUENCY(IF(E8:E1002="Win",ROW(E8:E1002)),IF(E8:E1002<>"Win",ROW(E8:E1002))))