Ranking Composite/Concatenated scores in place, greater than 9

[kyle21rob]

Hi-
I am trying to rank a set # of teams, in place, based off of three separate criteria to develop a “composite score”.
First criteria is ‘# of wins’ (column B), Second criteria is ‘total points’ (column C), Third criteria is ‘individual points’.
‘# of wins’ would carry the most value, followed by ‘total points’, and lastly ‘individual points’.
I am hoping to do this without macros, as my vba experience is limited.
I am able to do this easily through formulas, unless there are more than 9 teams. Perhaps to make it more clear as to what I am trying to do, here is what I’ve done that works well unless the # of teams is > 9.
Example:
Column A has the teams, for instance: A3 = Team 1, A4 = Team 2, A5 = Team 3, etc.
· B3 has the ‘# of wins’ for Team 1, C3 has the ‘total points’ of Team 1, D3 has the ‘individual points’ for Team 1. Row 4 would show the same information for Team 2, Row 5 for Team 3, and so on.
· E3, F3, G3 would use the Rank function to rank Team 1 (in the respective categories), compared to the other teams. =Rank(C3,$C3:$C14,0). Copied down for all teams, and over for the other 2 criteria.
· H3 uses the Concatenate function to have a composite score for Team 1. For instance, if Team 1 had the most wins (1 in E3), the second most total points (2 in F3), and the 4^th best individual points (4 in G3), their composite score would be 124. Using the same logic, if Team 2 tied in wins (1) and total points (2), but got second (2) in individual points their composite score would be 122.
· I3 would rank each team using the Rank function again, based off of the concatenated/composite score.
This works fine as long as you have less than 9 teams because the concatenated rank may provide a higher than 3-digit #. And I have than 9 teams.
There is probably a simple way to resolve this, but I am struggling. All non-VBA ideas are very appreciated!

 

[djrod1rep]

You can try to use this formula in Cell H3:

=VALUE(CONCATENATE(E3,F3,G3))

Then, in Cell I3, you can use the RANK function based off the Composite Score value:

=RANK(H3,$H$3:$H$17,0)

I was able to use a 15-team example from your details and it worked fine.
These were the Composite Score values I used:

15115
14314
13413
12212
111111
101010
999
888
777
666
555
4134
3143
2152
1111

Let me know if it works for you.

 

[kyle21rob]

Thanks for the response. Unfortunately, I believe this still has the same issue because of >9 (double-digit) # of teams. I probably didn't explain well enough as to why this is a problem, so I will try to clarify.

Example: Team 1 ends up with the following "Composite Score" 91010 (9-10-10), while Team 2 ends up with 10910 (10-9-10). In this scenario, each of these teams composite scores have 5 digits. Based on the rank of the Composite score, Team 1 should be ranked better than Team 2 because the most important/first criteria (wins rank, 9) is better than Team 2 (wins rank, 10). However, using this logic and RANK function Team 2 is calculated as better, incorrectly according to my ideal outcome. Essentially, saying Team 2 has a composite score of 10,910 which is lower/better than the composite score of Team 1 - 91,010. This issue doesn't happen if I have less than 10 (single digit) teams. I could be going about this completely the wrong way. Hopefully that clarifies what I am trying to do.

Thanks again for the response. Any other ideas are much appreciated.