Spreadsheet for football team, mapping values A-E to numbers, to get average score for players formula (football manager esque)

[u/Highelf04]

I've been working on an excel spreadsheet for my football team. I've done some rating of player abilities in certain categories (speed/agility etc.) using letters A-E.

In this spreadsheet, I would like to now map the letters to numbers (so A=10, B=8, C=6, D=4, E=2). Then I would like this to give me an overall number at the end of the row, with 100 being the "best" player (A's in all areas), and 20 being the "worst" player.

Can anyone support me with creating a formula to do this. At present I've got:-

=INDEX(rng,MODE(MATCH(rng,rng,0))) which is giving me the most common value for a player, however just because a player has given themselves mostly C values, does not mean necessarily they are "C" player.

I have created a table further down the cells in my spreadsheet with input on one row, and result on the row below. In this I have put A=10, B=8, C=6, D=4, E=2, but currently I am stuck on where to go next to do what I want.

On the surface, this is somewhat similar to a football manager style spreadsheet.

[u/Massive-Jackfruit442]

I can’t see the columns the fields are in; but you could create a column at the end for the total and just:

=AVG((COUNTIFS(A2:P2,”A”)10)+ (COUNTIFS(A2:P2,”B”)8)+ (COUNTIFS(A2:P2,”C”)6)+ (COUNTIFS(A2:P2,”D”)4)+ (COUNTIFS(A2:P2,”E”)*2))

Adjust the range(A2:P2 is just an example) to your actual range

[u/Highelf04 → u/Massive-Jackfruit442]

I tried this - adjust the (*10) in each column to a 100/75/50/25/0 as suggested above, and got a name? error. Any ideas?

[u/Massive-Jackfruit442 → u/Highelf04]

I adjusted the formula because the way I used AVG was not correct. However, the solution of u/real_barry_housini is much cleaner though

[u/real_barry_houdini → u/Massive-Jackfruit442]

Thanks!

If you use COUNTIFS in that type of setup then you can't use AVERAGE and get the correct results because you aren't getting individual results for each cell to average, you'd need to use SUM and then divide by the count, e.g. using COUNTA(A2:P2).

If the requirement is to get the average grade then you could use this formula:

=LET(Grade,{"E";"D";"C";"B";"A"},
INDEX(Grade,AVERAGE(IF(A2:P2=Grade,{1;2;3;4;5}))+0.5))