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

TKWSNx93

New Member
Joined
Dec 11, 2016
Messages
6
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
PFPA
PACEAway
PFPAPACE
Atlanta106.2104.398.9Atlanta96.3100.699.2
Boston103.7103.796.5 Boston105.7102.795.5
Brooklyn105.8112.2100.5 Brooklyn106.8116.9102.9
Charlotte104.1101.496.7Charlotte105.7101.197.2
Chicago105.799.696.2 Chicago101100.194.9
Cleveland115.2103.596Cleveland106103.697.1
Dallas93.998.191.6 Dallas92.1102.992.6
Denver109.2112.798.4 Denver102.7105.399.2
Detroit101.190.194.1 Detroit98.9100.794
Golden State123.9109.299.9 Golden State115.2102.6100.8
Houston111101.697.5 Houston112.1109.197.6
Indiana103.4100.397.6 Indiana105.5115.398.2
LA Clippers106.596.595.5 LA Clippers109.9102.896.1
LA Lakers109.7109.899.2 LA Lakers100.5111.599.2
Memphis94.994.393.6 Memphis100.6102.292.6
Miami98.4100.194.6 Miami95.4100.592.9
Milwaukee108.9104.497.8Milwaukee94.798.495.7
Minnesota104103.694.6Minnesota103.5
109.895.6
New Orleans107.2108.799 New Orleans94.5100.596.1
New York105.5105.395.8 New York102.5108.996.9
Oklahoma City109.6103.198.8 Oklahoma City103.1107.199
Orlando9297.692.9 Orlando95.8100.595.6
Philadelphia98.8104.497.4Philadelphia95.4109.995.9
Phoenix102.8110101.9 Phoenix108.6115101.1
Portland112112.698.1Portland106.7110.597.8
Sacramento103.5105.195.8 Sacramento102.7103.295
San Antonio95.196.692.8San Antonio106.998.493.8
Toronto111.599.593.9 Toronto110.3106.896
Utah100.194.191.5 Utah100.697.891.8
Washington105.110597.2 Washington101.9107.396.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!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
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'?
 
Last edited:

TKWSNx93

New Member
Joined
Dec 11, 2016
Messages
6
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

Banned
Joined
Mar 9, 2014
Messages
3,628

ADVERTISEMENT

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?
 

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628

ADVERTISEMENT

Just to be clear, I am not expecting payment! ;)
 

TKWSNx93

New Member
Joined
Dec 11, 2016
Messages
6
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

Active Member
Joined
May 10, 2007
Messages
463
Hi All,

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


In K2

=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))),"")

<tbody>
</tbody>

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

 
Last edited:

TKWSNx93

New Member
Joined
Dec 11, 2016
Messages
6
Hi All,

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


In K2

=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))),"")

<tbody>
</tbody>

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,470
Messages
5,601,840
Members
414,479
Latest member
Beau the dog

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
Top