Need help finding "Similar" Teams (I will pay anyone that can figure this out!)

[TKWSNx93]

I am comparing NBA teams and three of their stats on the road, and at home.

There are 30 teams. I am recording their Points per game at home, Points allowed at home, Pace at home, Points per game on the road, Points allowed on the road, and Pace on the road. This means a total of 6 numbers for each team.

Here is the data:

Home	PF	PA	PACE		Away	PF	PA	PACE
Atlanta	106.2	104.3	98.9		Atlanta	96.3	100.6	99.2
Boston	103.7	103.7	96.5		Boston	105.7	102.7	95.5
Brooklyn	105.8	112.2	100.5		Brooklyn	106.8	116.9	102.9
Charlotte	104.1	101.4	96.7		Charlotte	105.7	101.1	97.2
Chicago	105.7	99.6	96.2		Chicago	101	100.1	94.9
Cleveland	115.2	103.5	96		Cleveland	106	103.6	97.1
Dallas	93.9	98.1	91.6		Dallas	92.1	102.9	92.6
Denver	109.2	112.7	98.4		Denver	102.7	105.3	99.2
Detroit	101.1	90.1	94.1		Detroit	98.9	100.7	94
Golden State	123.9	109.2	99.9		Golden State	115.2	102.6	100.8
Houston	111	101.6	97.5		Houston	112.1	109.1	97.6
Indiana	103.4	100.3	97.6		Indiana	105.5	115.3	98.2
LA Clippers	106.5	96.5	95.5		LA Clippers	109.9	102.8	96.1
LA Lakers	109.7	109.8	99.2		LA Lakers	100.5	111.5	99.2
Memphis	94.9	94.3	93.6		Memphis	100.6	102.2	92.6
Miami	98.4	100.1	94.6		Miami	95.4	100.5	92.9
Milwaukee	108.9	104.4	97.8		Milwaukee	94.7	98.4	95.7
Minnesota	104	103.6	94.6		Minnesota	103.5	109.8	95.6
New Orleans	107.2	108.7	99		New Orleans	94.5	100.5	96.1
New York	105.5	105.3	95.8		New York	102.5	108.9	96.9
Oklahoma City	109.6	103.1	98.8		Oklahoma City	103.1	107.1	99
Orlando	92	97.6	92.9		Orlando	95.8	100.5	95.6
Philadelphia	98.8	104.4	97.4		Philadelphia	95.4	109.9	95.9
Phoenix	102.8	110	101.9		Phoenix	108.6	115	101.1
Portland	112	112.6	98.1		Portland	106.7	110.5	97.8
Sacramento	103.5	105.1	95.8		Sacramento	102.7	103.2	95
San Antonio	95.1	96.6	92.8		San Antonio	106.9	98.4	93.8
Toronto	111.5	99.5	93.9		Toronto	110.3	106.8	96
Utah	100.1	94.1	91.5		Utah	100.6	97.8	91.8
Washington	105.1	105	97.2		Washington	101.9	107.3	96.2

<colgroup><col><col span="2"><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

I need the teams to have similar numbers for all three categories. For example, Minnesota and New York are very similar because the numbers for Minnesota and New York are all within 2 points. We CANNOT find the average of all three numbers and compare them. For example, a team with 111points per game is NOT similar to a team with 99points per game even if their averages are similar.

I will pay anyone that can figure this out for me. This is a project I have been doing manually for months and it's about time I find a way to automate this!

 

[TKWSNx93]

Disregard that post I made, didn't see the download link. This is a great contribution!

 

[Canapone]

Hi,

thanks for sharing feedback.

Regards


A small typo in the second formula: sorry, I'm traslating from my Italian settings PC. In the file formulas should be OK.

=IFERROR(INDEX($A$2:$A$31,AGGREGATE(15,6,ROW($2:$31)-ROW($A$2)+1/((ABS($G$2:$G$31-$G2)<2)*(ABS($H$2:$H$31-$H2)<2)*(ABS($I$2:$I$31-$I2)<2)*($A$2:$A$31<>$A2)),COLUMNS($A$1:A$1))),"")

 

[AliGW]

Here's an adaptation of Canapone's solution (L2 is a drop-down list):

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Home	PF	PA	PACE		Away	PF	PA	PACE			Home	PF	PA	PACE		Away	PF	PA	PACE
2	Atlanta	106.2	104.3	98.9		Atlanta	96.3	100.6	99.2			Minnesota	104	103.6	94.6		Minnesota	103.5	109.8	95.6
3	Boston	103.7	103.7	96.5		Boston	105.7	102.7	95.5
4	Brooklyn	105.8	112.2	100.5		Brooklyn	106.8	116.9	102.9			New York	105.5	105.3	95.8		New York	102.5	108.9	96.9
5	Charlotte	104.1	101.4	96.7		Charlotte	105.7	101.1	97.2
6	Chicago	105.7	99.6	96.2		Chicago	101	100.1	94.9
7	Cleveland	115.2	103.5	96		Cleveland	106	103.6	97.1
8	Dallas	93.9	98.1	91.6		Dallas	92.1	102.9	92.6
9	Denver	109.2	112.7	98.4		Denver	102.7	105.3	99.2
10	Detroit	101.1	90.1	94.1		Detroit	98.9	100.7	94
11	Golden State	123.9	109.2	99.9		Golden State	115.2	102.6	100.8
12	Houston	111	101.6	97.5		Houston	112.1	109.1	97.6
13	Indiana	103.4	100.3	97.6		Indiana	105.5	115.3	98.2
14	LA Clippers	106.5	96.5	95.5		LA Clippers	109.9	102.8	96.1
15	LA Lakers	109.7	109.8	99.2		LA Lakers	100.5	111.5	99.2
16	Memphis	94.9	94.3	93.6		Memphis	100.6	102.2	92.6
17	Miami	98.4	100.1	94.6		Miami	95.4	100.5	92.9
18	Milwaukee	108.9	104.4	97.8		Milwaukee	94.7	98.4	95.7
19	Minnesota	104	103.6	94.6		Minnesota	103.5	109.8	95.6
20	New Orleans	107.2	108.7	99		New Orleans	94.5	100.5	96.1
21	New York	105.5	105.3	95.8		New York	102.5	108.9	96.9
22	Oklahoma City	109.6	103.1	98.8		Oklahoma City	103.1	107.1	99
23	Orlando	92	97.6	92.9		Orlando	95.8	100.5	95.6
24	Philadelphia	98.8	104.4	97.4		Philadelphia	95.4	109.9	95.9
25	Phoenix	102.8	110	101.9		Phoenix	108.6	115	101.1
26	Portland	112	112.6	98.1		Portland	106.7	110.5	97.8
27	Sacramento	103.5	105.1	95.8		Sacramento	102.7	103.2	95
28	San Antonio	95.1	96.6	92.8		San Antonio	106.9	98.4	93.8
29	Toronto	111.5	99.5	93.9		Toronto	110.3	106.8	96
30	Utah	100.1	94.1	91.5		Utah	100.6	97.8	91.8
31	Washington	105.1	105	97.2		Washington	101.9	107.3	96.2

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

Sheet1​

Worksheet Formulas Cell Formula M2 =INDEX($B$2:$D$31,MATCH($L2,$A$2:$A$31,0),MATCH(M1,$B$1:$D$1,0)) N2 =INDEX($B$2:$D$31,MATCH($L2,$A$2:$A$31,0),MATCH(N1,$B$1:$D$1,0)) O2 =INDEX($B$2:$D$31,MATCH($L2,$A$2:$A$31,0),MATCH(O1,$B$1:$D$1,0)) Q2 =L2 R2 =INDEX($G$2:$I$31,MATCH($L2,$A$2:$A$31,0),MATCH(R1,$G$1:$I$1,0)) S2 =INDEX($G$2:$I$31,MATCH($L2,$A$2:$A$31,0),MATCH(S1,$G$1:$I$1,0)) T2 =INDEX($G$2:$I$31,MATCH($L2,$A$2:$A$31,0),MATCH(T1,$G$1:$I$1,0)) Q4 =L4 R4 =IFERROR(INDEX($G$2:$I$31,MATCH($L4,$A$2:$A$31,0),MATCH(R$1,$G$1:$I$1,0)),"") S4 =IFERROR(INDEX($G$2:$I$31,MATCH($L4,$A$2:$A$31,0),MATCH(S$1,$G$1:$I$1,0)),"") T4 =IFERROR(INDEX($G$2:$I$31,MATCH($L4,$A$2:$A$31,0),MATCH(T$1,$G$1:$I$1,0)),"") M4 =IFERROR(INDEX($B$2:$D$31,MATCH($L4,$A$2:$A$31,0),MATCH(M$1,$B$1:$D$1,0)),"") N4 =IFERROR(INDEX($B$2:$D$31,MATCH($L4,$A$2:$A$31,0),MATCH(N$1,$B$1:$D$1,0)),"") O4 =IFERROR(INDEX($B$2:$D$31,MATCH($L4,$A$2:$A$31,0),MATCH(O$1,$B$1:$D$1,0)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula L4 {=IFERROR(INDEX($A$2:$A$31,AGGREGATE(15,6,ROW($2:$31)-ROW($A$2)+1/((ABS($B$2:$B$31-$M$2)<=2)*(ABS($C$2:$C$31-$N$2)<=2)*(ABS($D$2:$D$31-$O$2)<=2)*(ABS($G$2:$G$31-$R$2)<=2)*(ABS($H$2:$H$31-$S$2)<=2)*(ABS($I$2:$I$31-$T$2)<=2)*($A$2:$A$31<>$L$2)),ROWS($A$1:A1))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>