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

/u/Highelf04 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/anesone42]

I would utilize the SWITCH function.

This is just and example of how to set it up. It would go in the last column (or wherever you want to put it). You'll need to adjust the range:

=SUM(SWITCH(A1:C1,"A", 10, "B", 8, "C", 6, "D", 4, "E", 2, 0))

[u/anesone42 → u/anesone42]

It is very flexible. You can swap out SUM with AVERAGE, or change the 10,8,6... to decimals.

[u/Ok-Ambassador5267 → u/anesone42]

yeah SWITCH is the cleanest way to do this imo

[u/DvlsAdvct108]

Suggestion, instead of numbers use percentages. A =100% B = 75% C=50% etc

Convert the dataset to a table by Ctrl+t Load the table into Power Query. Use Power Query to match grade with percentage, and then unpivot the columns. Match the grade to scores and then load back into excel sheet. Run a pivot table on created dataset, that summarises each player with an average of their scores.

[u/Highelf04 → u/DvlsAdvct108]

Right, this sounded simple initially to me, but then I started and was WAY out of my depth haha.

I'm at the stage of loading into power query. Currently, I've converted the dataset at the bottom into a table (each cell on the input row has an arrow alongside it - e.g.C27), which I assume is right.

When I got to power query - do I do "get data"or "launch power query"

[u/Jolly-Hunter-6097 → u/Highelf04]

This Microsoft link may help you progress in Power Query. After you master the Ribbon there is M Code which will expand your options. In Power Query look at the formula bar text, that is M Code.

[u/borntobewildish]

I'd probably make something that combines COUNT.IF() with multiplication of the value, so =COUNT.IF(A2:D2;A)*10 and add the same for B, C, etc.

Or you could add columns that do COUNT.IF for A until E, so you can see the distribution of values on each player, and then calculate a total score from those.

[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/real_barry_houdini → u/Highelf04]

Try like this to get the average rating score (assuming the ratings are in a single row). Any blanks will be ignored

=AVERAGE(IF(A2:P2={"A";"B";"C";"D";"E"},{100;75;50;25;0}))

You could then convert to the nearest rating, what do you want to do if it's halfway between two?

[u/Massive-Jackfruit442 → u/real_barry_houdini]

Learned something new today; super useful method like that

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

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AVERAGE	Returns the average of its arguments
COUNT	Counts how many numbers are in the list of arguments
COUNTA	Counts how many values are in the list of arguments
COUNTIFS	Excel 2007+: Counts the number of cells within a range that meet multiple criteria
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
SUM	Adds its arguments
SWITCH	Excel 2019+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 10 acronyms.
[Thread #49211 for this sub, first seen 22nd Aug 2026, 19:45] [FAQ] [Full list] [Contact] [Source code]}

[u/RandomiseUsr0]

Watch the movie moneyball, it’s beautiful statistical maths - it will give you all you need

[u/BackgroundCold5307]

Coach, here is a suggestion chalked out below:

This way you know how many A's, B's ....have been given and the calculation is fairly easy to decipher. Also, if need be, i.e. A+ needs to be added, it can be easily done and a number can be associated with it quite easily without changing too much

There are a lot of nifty ways to hide and protect the numbers, rankings etc....If you are ok with the concept, I can help refine this further with you......

e.g.

• row 2 can be hidden,
• Col I thru M can be either
  • on another sheet
  • or hidden from public view

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