Ranking Formulas

[SJB777]

Hi, I'm wondering if anyone can help me with a formula that would be very advanced for my knowledge (and if it is even possible to do). Any advice much appreciated.

I am looking to make a scoring system for 'players' that appear on a list.


Player Lists.jpg


The first formula I would require for the attached picture would be to calculate the rank score of each player:

For being placed first in a list 'Player 1' receives 10 'points'. Last place in a list = 1 point. Not featuring on a list = 0 points. The formula would therfore need to add up the total of the cells next to where each player features.



The second formula would work out how many lists each player features in (simply counts how many lists they appear in):

Appearing in a list gives a player 1 'point' not appearing = 0 points. The formula would therfore count how many lists a player appears in and give them a score (0-3 in the example)


No idea if either of these are possible but any advice appreciated.

Many, many thanks.

[SJB777]

It's been a long day, for anyone confused by my fat fingers 'player 2''s rank score should read 28 on the image not that it affects my formula questions in any way.

[Melvinrobb]

Can you please attach an excel file with the data in the image, so possible solutions can be tested.

[SJB777]

Attached. Thanks for looking into this.Ranking System.xlsx

[dluhut]

Please see attached for solution.

Solution will not "automatic" since you've created in list. Thus have to modified the code every time when there's new lists.

[SJB777]

Fantastic many thanks for doing that, much appreciated.

I understand that "(IFERROR(11-INDEX(RankList1,MATCH($A16,PlayerList1,0)),0)" is one "set" of the formula.

Could you tell me how I create another "Rank List" ie how did you get the formula to identify these groups of cells as a "rank list".
Once I know how to do that can I simply add these on to the end of every formula?

Thank You

[dluhut]

Yes you are right.

To create the "name range" like RankList1 or PlayerList1, Below are the steps.

Highlight the range of column (J4:K13), Assuming in the next list is from J4 to J13, on the upper Left Hand corner of Excel, where it says J14, left click and Type 'RankList4' and Enter.

[SJB777]

Brilliant. Thank you very much for your help.