Need help finding closest comparable player in a database

alh20

New Member
Joined
Apr 26, 2018
Messages
5
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
 

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.
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
 
Upvote 0
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.
 
Upvote 0
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) ?
 
Upvote 0
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.
 
Upvote 0
how do i add an excel-file (650kB) here on the site ?
 
Upvote 0
testing
Best Comparable Template .xlsb
CPCQCRCSCTCUCVCWCX
1Metric 54Metric 55Best ComparableBest Comparable #2Best Comparable #3Best Comparable #4Best Comparable #5FG
21,1N/ATalhah CoteTayyab ChanJulio BullockBradlee ParrishTobey Kelly#N/B#N/B
31,0N/ASacha BruceJordyn CampbellJarod ShepherdTorin GriffithKester Juarez02,4
41,2N/AConrad HoustonHarlan LinElias HooperMitchell StarkDru Crawford#N/B#N/B
51,1N/AArturo McmillanCordell MosesTony GillespieJaya WatsonGurveer Gough#N/B#N/B
63,1N/AJorgie KaurDeja GoodNicola ColemanHaaris DawsonMoses Aguirre#N/B0
71,1N/ASacha BruceBrody OrozcoIrfan SinghKaiden LiJarod Shepherd06,4
83,3N/ACamryn WattsGage BlairLorcan HartShawn JamesKory Muir07,2
94,3N/ASpencer WagnerEoin WarnerVivaan SheldonEesa HarwoodHussein Dale#N/B#N/B
101,0N/ACem McintoshJodi GilmoreAmani MacgregorMatthew BentonVincent Dalby#N/B#N/B
EDGE
Cell Formulas
RangeFormula
CW2:CW10CW2=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:CX10CX2=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
NameRefers ToCells
Pointer=EDGE!$EA$1CW2:CX10
 
Upvote 0
Cell Formulas
RangeFormula
EB2:EH2EB2=AGGREGATE(15,6,$DY$2:$DY$638,EB1)
EA3EA3=INDEX($B$2:$B$638,Pointer)
EB3:EH3EB3=INDEX($B$2:$B$638,MATCH(EB2,$DY$2:$DY$638,0))
Named Ranges
NameRefers ToCells
Name=EDGE!$B$2:$B$643EA3:EH3
Pointer=EDGE!$EA$1EA3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
EA3:EB3Expression=$EA$3<>$EB$3textNO
EB2Expression=$EB$2<>0textNO
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top