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?

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

<tbody>
</tbody>

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).

AB/H	H Rank
2.80	1
2.85	2
2.94	3
3.04	4
3.05	5
3.05	6
3.07	7
3.12	8
3.14	9
3.15	10
3.16	11
3.18	12
3.19	13
3.21	14
3.21	15
3.24	16
3.25	17
3.31	18
3.31	19
3.33	20

<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

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.