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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,851
Messages
5,833,965
Members
430,249
Latest member
Muka

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