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:

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

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!

 

[AliGW]

So what exactly are the criteria? Do PF, PA and PACE all have to be within 2 points either way for it to count as a 'match'?

 

[TKWSNx93]

So what exactly are the criteria? Do PF, PA and PACE all have to be within 2 points either way for it to count as a 'match'?

This is correct.

 

[TKWSNx93]

Ideally, I want a table for each team that shows what other teams they are "similar" to. I want to be able to plug in new numbers everyday and have the formula(s) calculate "similar teams" each time

 

[AliGW]

And how should we distinguish between what is in the home column and what is in the away? Do they need to be treated separately?

I think the best layout would be to have a drop-down to select the team and then have the table populate with similar teams - would that work for you?

 

[mole999]

@ TKWSNx93 Please read and understand forum rules which you agreed to when joining the site > http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html #5

 

[AliGW]

Just to be clear, I am not expecting payment!

 

[TKWSNx93]

And how should we distinguish between what is in the home column and what is in the away? Do they need to be treated separately?

I think the best layout would be to have a drop-down to select the team and then have the table populate with similar teams - would that work for you?

All home and away numbers need to be seperate. We do not need to find a "similar" away team's numbers to a home team. Only home to home, and away to away.

That format would be perfect!

 

[Canapone]

Hi All,

numbers in columns BCD (home) e GHI (away)


In K2

[TABLE="width: 1243"]
<tbody>[TR]
[TD="width: 1243"]=IFERROR(INDEX($A$2:$A$31,AGGREGATE(15,6,ROW($2:$31)-ROW($A$2)+1/((ABS($B$2:$B$31-$B2)<2)*(ABS($C$2:$C$31-$C2)<2)*(ABS($D$2:$D$31-$D2)<2)*($A$2:$A$31<>$A2)),COLUMNS($A$1:A$1))),"")[/TD]
[/TR]
</tbody>[/TABLE]

to be copied to the right untill column O (if it's enough) and down


In Q2 down and to the right

=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-$G2)<2)*(ABS($I$2:$I$31-$I2)<2)*($A$2:$A$31<>$A2)),COLUMNS($A$1:A$1))),"")


It's a first approach


Please refer to this file

https://dl.dropboxusercontent.com/u/106022761/salutidaFirenze.xlsx

 

[TKWSNx93]

Hi All,

numbers in columns BCD (home) e GHI (away)


In K2

[TABLE="width: 1243"]
<tbody>[TR]
[TD="width: 1243"]=IFERROR(INDEX($A$2:$A$31,AGGREGATE(15,6,ROW($2:$31)-ROW($A$2)+1/((ABS($B$2:$B$31-$B2)<2)*(ABS($C$2:$C$31-$C2)<2)*(ABS($D$2:$D$31-$D2)<2)*($A$2:$A$31<>$A2)),COLUMNS($A$1:A$1))),"")[/TD]
[/TR]
</tbody>[/TABLE]

to be copied to the right untill column O (if it's enough) and down


In Q2 down and to the right

=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-$G2)<2)*(ABS($I$2:$I$31-$I2)<2)*($A$2:$A$31<>$A2)),COLUMNS($A$1:A$1))),"")


It's a first approach


Please refer to this file

https://dl.dropboxusercontent.com/u/106022761/salutidaFirenze.xlsx

Wow! This worked perfectly for the home teams. I was having issues getting it to work with the Away (your second code). All I need now is a way to get this formatted nicely. Thank you so much