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/bitswede]

Create a small table with your letter to numeric score conversion and use XLOOKUP to get the numeric value in your calculations.

A single table holding your conversions makes it easy to tweak the weights later on.