Need help finding closest comparable player in a database

[alh20]

Hi, everyone. I've got an issue that I've been trying to solve for some time now, but I just can't quite get a formula to work how I want it to. In the worksheet linked below, I have a list of players' names and some metrics that I can use to compare them to each other. My goal is to have a formula that will work as a Nearest Neighbor Search and to have five cells at the end of each row where this formula can be used to find out each player's most comparable, 2nd most comparable, 3rd most comparable, 4th most comparable and 5th most comparable players. In columns CW to DX, I have a breakdown of how all the metrics are supposed to factor into this 'most comparable' formula. In column DY, we get a total for how each of the players compare to the first player on the list (Piper Pickett), with the player with the lowest number being the most comparable player. According to cells DZ3:ED3, the most comparable player for Piper Pickett (row 2) should be Talhah Cote (row 279), the 2nd most comparable should be Tayyab Chan, the 3rd most comparable should be Julio Bullock, the 4th most comparable should be Bradlee Parrish and the 5th most comparable should be Tobey Kelley. Instead, in cells CR2:CV2, I get Cairo Beck, Parker Greene, Talhah Cote, Rida Reyna and Rhys Osborn as the top 5 most comparable players, which are all wrong. Do any of you have any ideas on how I could fix these formulas to give me the correct results?

Link: Best Comparable Template.xlsx

 

[BSALV]

the results of the formulas in columns CR:CV is completely different of the result in DY, factor 100
So the method isn't exact the same

 

[alh20]

the results of the formulas in columns CR:CV is completely different of the result in DY, factor 100
So the method isn't exact the same

Yeah, the results in columns CW:DY are just to show how I want each metric on the worksheet to factor into the formula, because some metrics (column BC) are more important than others (column AN). The results in columns CR:CV, on the other hand, are just how far I can take these formulas before I just completely mess everything up.

 

[BSALV]

the formula used to give a weight at every metric, is that easy to explain ?
When I see that huge formula error seeking is almost impossible.
Can it be done with macros, VBA (UDF) ?

 

[alh20]

the formula used to give a weight at every metric, is that easy to explain ?
When I see that huge formula error seeking is almost impossible.
Can it be done with macros, VBA (UDF) ?

Yeah, so what I'm ultimately aiming for is a formula that starts out by checking the heights and weights of each player and disregarding any players who are 2 inches taller/shorter than or 10 pounds heavier/lighter than the original player they're being compared to. Then I'm trying to combine into the remainder of this larger formula all the smaller, individual formulas (with weights) found from columns I:CC and find out which row would, in the end, have the lowest sum based on all those smaller formulas (as seen in totals found in column DY). Finally, based on that lowest sum, I want it to spit out the player's name that matches that lowest number. In this case of the worksheet I linked, the lowest (non-zero) number in column DY is 69.0071 (row 279) and the player associated with that lowest number is Talhah Cote - so Cote would be Piper Pickett's (row 2) closest comparison.

If you need a better explanation for any part of this post, just let me know and I'll try to clarify as best as I can. Also, I don't really have a particular preference for how it's done. Whether it's through Excel formulas or VBA, I just want it to work.

 

[BSALV]

how do i add an excel-file (650kB) here on the site ?

 

[Fluff]

how do i add an excel-file (650kB) here on the site ?

You can't, however you can use the Xl2BB add-in to post data to the site, along with any formulae & if your posting code, just post it to the site using code tags

 

[BSALV]

testing

Best Comparable Template .xlsb
	CP	CQ	CR	CS	CT	CU	CV	CW	CX
1	Metric 54	Metric 55	Best Comparable	Best Comparable #2	Best Comparable #3	Best Comparable #4	Best Comparable #5	F	G
2	1,1	N/A	Talhah Cote	Tayyab Chan	Julio Bullock	Bradlee Parrish	Tobey Kelly	#N/B	#N/B
3	1,0	N/A	Sacha Bruce	Jordyn Campbell	Jarod Shepherd	Torin Griffith	Kester Juarez	0	2,4
4	1,2	N/A	Conrad Houston	Harlan Lin	Elias Hooper	Mitchell Stark	Dru Crawford	#N/B	#N/B
5	1,1	N/A	Arturo Mcmillan	Cordell Moses	Tony Gillespie	Jaya Watson	Gurveer Gough	#N/B	#N/B
6	3,1	N/A	Jorgie Kaur	Deja Good	Nicola Coleman	Haaris Dawson	Moses Aguirre	#N/B	0
7	1,1	N/A	Sacha Bruce	Brody Orozco	Irfan Singh	Kaiden Li	Jarod Shepherd	0	6,4
8	3,3	N/A	Camryn Watts	Gage Blair	Lorcan Hart	Shawn James	Kory Muir	0	7,2
9	4,3	N/A	Spencer Wagner	Eoin Warner	Vivaan Sheldon	Eesa Harwood	Hussein Dale	#N/B	#N/B
10	1,0	N/A	Cem Mcintosh	Jodi Gilmore	Amani Macgregor	Matthew Benton	Vincent Dalby	#N/B	#N/B
EDGE

Cell Formulas
Range	Formula
CW2:CW10	CW2	=IF(OR((ABS(EDGE!$F$2:$F$643-INDEX(EDGE!$F$2:$F$643,Pointer))*4)>300,(ABS(EDGE!$F$2:$F$643-INDEX(EDGE!$F$2:$F$643,Pointer))*4)>=8),#N/A,ABS(EDGE!$F$2:$F$643-INDEX(EDGE!$F$2:$F643,Pointer))*4)
CX2:CX10	CX2	=IF((ABS(EDGE!$G$2:$G$643-INDEX(EDGE!$G$2:$G$643,Pointer))*0.8)>100,#N/A,IF((ABS(EDGE!$G$2:$G$643-INDEX(EDGE!$G$2:$G$643,Pointer))<=10),(ABS(EDGE!$G$2:$G$643-INDEX(EDGE!$G$2:$G$643,Pointer))*0.8),#N/A))

Named Ranges
Name	Refers To	Cells
Pointer	=EDGE!$EA$1	CW2:CX10

 

[BSALV]

Best Comparable Template .xlsb
	DZ	EA	EB	EC	ED	EE	EF	EG	EH
1		637	1	2	3	4	5	6	7
2			3,00	45,88	61,67	62,03	74,87	80,68	86,22
3		Deandre Mcclure	Deandre Mcclure	Adrianna Cortez	Brittany Doherty	Brook Whyte	Heath Rowe	Aditya Dalton	Kale Glenn
4
5
6
7
8
9
10
11
EDGE

Cell Formulas
Range	Formula
EB2:EH2	EB2	=AGGREGATE(15,6,$DY$2:$DY$638,EB1)
EA3	EA3	=INDEX($B$2:$B$638,Pointer)
EB3:EH3	EB3	=INDEX($B$2:$B$638,MATCH(EB2,$DY$2:$DY$638,0))

Named Ranges
Name	Refers To	Cells
Name	=EDGE!$B$2:$B$643	EA3:EH3
Pointer	=EDGE!$EA$1	EA3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
EA3:EB3	Expression	=$EA$3<>$EB$3	text	NO
EB2	Expression	=$EB$2<>0	text	NO