Assign each category points then sort total points from lowest to highest

[15minoffame]

I've searched Google but not able to find this answer because I don't think I entered the correct search verbiage. Does one need to have a complicated VBA in order for me to do what I want? I want to give points to each category where the player is ranked then ranked from lowest.

Example. Ty Cobb is ranked 1 in BA, 3rd in G, 3rd in AB, 2nd in Runs, ect. So you get 1 pt for BA, 3 pts for G & AB, 2 for Runs. Lets say Cobb gets 20 pts when you add up all the major offensive categories. How would you have a formula to add up all the points then sort them from lowest?

[TABLE="width: 695"]
<tbody>[TR]
[TD][TABLE="width: 695"]
<tbody>[TR]
[TD]Rk[/TD]
[TD]Player[/TD]
[TD]BA[/TD]
[TD]G[/TD]
[TD]AB[/TD]
[TD]R[/TD]
[TD]H[/TD]
[TD]2B[/TD]
[TD]3B[/TD]
[TD]HR[/TD]
[TD]RBI[/TD]
[TD]BB[/TD]
[TD]SO[/TD]
[TD]SB[/TD]
[TD]OBP[/TD]
[TD]SLG[/TD]
[TD]OPS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ty Cobb[/TD]
[TD="align: right"]0.366[/TD]
[TD="align: right"]3034[/TD]
[TD="align: right"]11434[/TD]
[TD="align: right"]2244[/TD]
[TD="align: right"]4189[/TD]
[TD="align: right"]724[/TD]
[TD="align: right"]295[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]1944[/TD]
[TD="align: right"]1249[/TD]
[TD="align: right"]680[/TD]
[TD="align: right"]897[/TD]
[TD="align: right"]0.433[/TD]
[TD="align: right"]0.512[/TD]
[TD="align: right"]0.945[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rogers Hornsby[/TD]
[TD="align: right"]0.358[/TD]
[TD="align: right"]2259[/TD]
[TD="align: right"]8173[/TD]
[TD="align: right"]1579[/TD]
[TD="align: right"]2930[/TD]
[TD="align: right"]541[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]301[/TD]
[TD="align: right"]1584[/TD]
[TD="align: right"]1038[/TD]
[TD="align: right"]679[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]0.434[/TD]
[TD="align: right"]0.577[/TD]
[TD="align: right"]1.01[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tris Speaker[/TD]
[TD="align: right"]0.345[/TD]
[TD="align: right"]2789[/TD]
[TD="align: right"]10195[/TD]
[TD="align: right"]1882[/TD]
[TD="align: right"]3514[/TD]
[TD="align: right"]792[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]1531[/TD]
[TD="align: right"]1381[/TD]
[TD="align: right"]393[/TD]
[TD="align: right"]436[/TD]
[TD="align: right"]0.428[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.928[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ted Williams[/TD]
[TD="align: right"]0.344[/TD]
[TD="align: right"]2292[/TD]
[TD="align: right"]7706[/TD]
[TD="align: right"]1798[/TD]
[TD="align: right"]2654[/TD]
[TD="align: right"]525[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]521[/TD]
[TD="align: right"]1839[/TD]
[TD="align: right"]2021[/TD]
[TD="align: right"]709[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]0.482[/TD]
[TD="align: right"]0.634[/TD]
[TD="align: right"]1.116[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Babe Ruth[/TD]
[TD="align: right"]0.342[/TD]
[TD="align: right"]2503[/TD]
[TD="align: right"]8399[/TD]
[TD="align: right"]2174[/TD]
[TD="align: right"]2873[/TD]
[TD="align: right"]506[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]714[/TD]
[TD="align: right"]2214[/TD]
[TD="align: right"]2062[/TD]
[TD="align: right"]1330[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]0.474[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]1.164[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Harry Heilmann[/TD]
[TD="align: right"]0.342[/TD]
[TD="align: right"]2147[/TD]
[TD="align: right"]7787[/TD]
[TD="align: right"]1291[/TD]
[TD="align: right"]2660[/TD]
[TD="align: right"]542[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]183[/TD]
[TD="align: right"]1543[/TD]
[TD="align: right"]856[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]0.41[/TD]
[TD="align: right"]0.52[/TD]
[TD="align: right"]0.93[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Bill Terry[/TD]
[TD="align: right"]0.341[/TD]
[TD="align: right"]1721[/TD]
[TD="align: right"]6428[/TD]
[TD="align: right"]1120[/TD]
[TD="align: right"]2193[/TD]
[TD="align: right"]373[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]1078[/TD]
[TD="align: right"]537[/TD]
[TD="align: right"]449[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]0.393[/TD]
[TD="align: right"]0.506[/TD]
[TD="align: right"]0.899[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Lou Gehrig[/TD]
[TD="align: right"]0.340[/TD]
[TD="align: right"]2164[/TD]
[TD="align: right"]8001[/TD]
[TD="align: right"]1888[/TD]
[TD="align: right"]2721[/TD]
[TD="align: right"]534[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]493[/TD]
[TD="align: right"]1995[/TD]
[TD="align: right"]1508[/TD]
[TD="align: right"]790[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]0.447[/TD]
[TD="align: right"]0.632[/TD]
[TD="align: right"]1.08[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]George Sisler[/TD]
[TD="align: right"]0.340[/TD]
[TD="align: right"]2055[/TD]
[TD="align: right"]8267[/TD]
[TD="align: right"]1284[/TD]
[TD="align: right"]2812[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]1178[/TD]
[TD="align: right"]472[/TD]
[TD="align: right"]327[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]0.379[/TD]
[TD="align: right"]0.468[/TD]
[TD="align: right"]0.847[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Tony Gwynn[/TD]
[TD="align: right"]0.338[/TD]
[TD="align: right"]2440[/TD]
[TD="align: right"]9288[/TD]
[TD="align: right"]1383[/TD]
[TD="align: right"]3141[/TD]
[TD="align: right"]543[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]1138[/TD]
[TD="align: right"]790[/TD]
[TD="align: right"]434[/TD]
[TD="align: right"]319[/TD]
[TD="align: right"]0.388[/TD]
[TD="align: right"]0.459[/TD]
[TD="align: right"]0.847[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Nap Lajoie[/TD]
[TD="align: right"]0.336[/TD]
[TD="align: right"]1988[/TD]
[TD="align: right"]7498[/TD]
[TD="align: right"]1083[/TD]
[TD="align: right"]2522[/TD]
[TD="align: right"]510[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1141[/TD]
[TD="align: right"]457[/TD]
[TD="align: right"]273[/TD]
[TD="align: right"]293[/TD]
[TD="align: right"]0.382[/TD]
[TD="align: right"]0.451[/TD]
[TD="align: right"]0.833[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Al Simmons[/TD]
[TD="align: right"]0.334[/TD]
[TD="align: right"]2215[/TD]
[TD="align: right"]8759[/TD]
[TD="align: right"]1507[/TD]
[TD="align: right"]2927[/TD]
[TD="align: right"]539[/TD]
[TD="align: right"]149[/TD]
[TD="align: right"]307[/TD]
[TD="align: right"]1828[/TD]
[TD="align: right"]615[/TD]
[TD="align: right"]737[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]0.38[/TD]
[TD="align: right"]0.535[/TD]
[TD="align: right"]0.915[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Paul Waner[/TD]
[TD="align: right"]0.333[/TD]
[TD="align: right"]2549[/TD]
[TD="align: right"]9459[/TD]
[TD="align: right"]1627[/TD]
[TD="align: right"]3152[/TD]
[TD="align: right"]605[/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]1309[/TD]
[TD="align: right"]1091[/TD]
[TD="align: right"]376[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]0.404[/TD]
[TD="align: right"]0.473[/TD]
[TD="align: right"]0.878[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Eddie Collins[/TD]
[TD="align: right"]0.333[/TD]
[TD="align: right"]2826[/TD]
[TD="align: right"]9949[/TD]
[TD="align: right"]1821[/TD]
[TD="align: right"]3315[/TD]
[TD="align: right"]438[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]1299[/TD]
[TD="align: right"]1499[/TD]
[TD="align: right"]467[/TD]
[TD="align: right"]741[/TD]
[TD="align: right"]0.424[/TD]
[TD="align: right"]0.429[/TD]
[TD="align: right"]0.853[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Stan Musial[/TD]
[TD="align: right"]0.331[/TD]
[TD="align: right"]3026[/TD]
[TD="align: right"]10972[/TD]
[TD="align: right"]1949[/TD]
[TD="align: right"]3630[/TD]
[TD="align: right"]725[/TD]
[TD="align: right"]177[/TD]
[TD="align: right"]475[/TD]
[TD="align: right"]1951[/TD]
[TD="align: right"]1599[/TD]
[TD="align: right"]696[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]0.417[/TD]
[TD="align: right"]0.559[/TD]
[TD="align: right"]0.976[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Heinie Manush[/TD]
[TD="align: right"]0.330[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]7654[/TD]
[TD="align: right"]1288[/TD]
[TD="align: right"]2524[/TD]
[TD="align: right"]491[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]1183[/TD]
[TD="align: right"]506[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]0.377[/TD]
[TD="align: right"]0.479[/TD]
[TD="align: right"]0.856[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Wade Boggs[/TD]
[TD="align: right"]0.328[/TD]
[TD="align: right"]2439[/TD]
[TD="align: right"]9180[/TD]
[TD="align: right"]1513[/TD]
[TD="align: right"]3010[/TD]
[TD="align: right"]578[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]1014[/TD]
[TD="align: right"]1412[/TD]
[TD="align: right"]745[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]0.415[/TD]
[TD="align: right"]0.443[/TD]
[TD="align: right"]0.858[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Rod Carew[/TD]
[TD="align: right"]0.328[/TD]
[TD="align: right"]2469[/TD]
[TD="align: right"]9315[/TD]
[TD="align: right"]1424[/TD]
[TD="align: right"]3053[/TD]
[TD="align: right"]445[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]1015[/TD]
[TD="align: right"]1018[/TD]
[TD="align: right"]1028[/TD]
[TD="align: right"]353[/TD]
[TD="align: right"]0.393[/TD]
[TD="align: right"]0.429[/TD]
[TD="align: right"]0.822[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Joe DiMaggio[/TD]
[TD="align: right"]0.325[/TD]
[TD="align: right"]1736[/TD]
[TD="align: right"]6821[/TD]
[TD="align: right"]1390[/TD]
[TD="align: right"]2214[/TD]
[TD="align: right"]389[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]361[/TD]
[TD="align: right"]1537[/TD]
[TD="align: right"]790[/TD]
[TD="align: right"]369[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0.398[/TD]
[TD="align: right"]0.579[/TD]
[TD="align: right"]0.977[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Jimmie Foxx[/TD]
[TD="align: right"]0.325[/TD]
[TD="align: right"]2317[/TD]
[TD="align: right"]8134[/TD]
[TD="align: right"]1751[/TD]
[TD="align: right"]2646[/TD]
[TD="align: right"]458[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]534[/TD]
[TD="align: right"]1922[/TD]
[TD="align: right"]1452[/TD]
[TD="align: right"]1311[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]0.428[/TD]
[TD="align: right"]0.609[/TD]
[TD="align: right"]1.038[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any help & suggestions.

 

[jmacleary]

I have assumed your data starts at A1. You will need to add some extra columns. For each offensive category (sorry I'm from UK so don't understand the abbbreviations) create a column with this formula in row 2 (replace C with the column number of the category): =RANK(C2,C$2:C$21) and copy down all rows. Then create 1 further column with this formula (where R and W are the start and end columns of your rank columns): =SUM(R2:W2) and copy down all rows. Finally, add 1 more column with this formula (assumes X is the column of sums): =RANK(X2,X$2:X$21,1). You can then sort the whole table on this column. I did it this way so you can see the steps involved - just hide the intermediate columns if you don't want to see them.

 

[15minoffame]

Hi,
This is what I'm looking for! Thank you so much for your help!

I have assumed your data starts at A1. You will need to add some extra columns. For each offensive category (sorry I'm from UK so don't understand the abbbreviations) create a column with this formula in row 2 (replace C with the column number of the category): =RANK(C2,C$2:C$21) and copy down all rows. Then create 1 further column with this formula (where R and W are the start and end columns of your rank columns): =SUM(R2:W2) and copy down all rows. Finally, add 1 more column with this formula (assumes X is the column of sums): =RANK(X2,X$2:X$21,1). You can then sort the whole table on this column. I did it this way so you can see the steps involved - just hide the intermediate columns if you don't want to see them.

 

[15minoffame]

I've ran into ties issue. How would you show both 3.21 as 14 but keep 3.24 as 16? This is the formula I have right now: =RANK(AE2,AE$2:AE$78,1).

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]AB/H[/TD]
[TD="class: xl65, width: 64"]H Rank[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2.80[/TD]
[TD="class: xl68, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2.85[/TD]
[TD="class: xl68, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2.94[/TD]
[TD="class: xl68, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.04[/TD]
[TD="class: xl68, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.05[/TD]
[TD="class: xl68, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.05[/TD]
[TD="class: xl68, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.07[/TD]
[TD="class: xl68, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.12[/TD]
[TD="class: xl68, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.14[/TD]
[TD="class: xl68, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.15[/TD]
[TD="class: xl68, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.16[/TD]
[TD="class: xl68, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.18[/TD]
[TD="class: xl68, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.19[/TD]
[TD="class: xl68, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.21[/TD]
[TD="class: xl68, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.21[/TD]
[TD="class: xl68, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.24[/TD]
[TD="class: xl68, align: right"]16[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.25[/TD]
[TD="class: xl68, align: right"]17[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.31[/TD]
[TD="class: xl68, align: right"]18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.31[/TD]
[TD="class: xl68, align: right"]19[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3.33[/TD]
[TD="class: xl68, align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]

Thanks again!

 

[jmacleary]

Hi there

The way rank works will give the same rank to identical values (in this case 3.21), however I think the actual rankings are slightly different which is why they show as different ranks. Try formatting the AB/H column to have all decimals shown and you shoud see a difference. If you actually want to work to 2 decimal places only, you can use the round function on the values in the AB/H column to get the result you want.