How to give text a numerical value in Excel

Question

I am trying to create a grading calculator for my undergrad students and I was wondering if there was a way to give text a numerical value so that the mean can be calculated to give a final grade. For example an 'Exceptional First' would equal 16.

To provide context the way we calculate the grades is to give a grade for each criteria being marked and then calculate the mean to get the final grade for that piece of work. So lets say for this example there are 3 criteria and the student got a mid first for criteria 1 which would equal 14, then a high first for criteria two which would equal 15 and then finally a Mid 2:2 for the third criteria which equals 8, their final grade would be calculated by the Mean so it would be a High 2:1 (which would be 12 because of the mean).

I need a way to show this in excel however instead of giving a final number it shows it as the text grade. So if my mean calculated out at 16 excel would show that as an exceptional first.

I have tried some IF functions and a SpellNumber function, however I have not used Excel much and am unsure if this is even something that can be done. I would appreciate any help!

Answer 1

Hello,

Another Excel user here.

I can give you two options.

The first one is to create an additional column to translate each of the texts to a numerical value. For example:

Note that I am using =IFS(D7="High",15,D7="Mid",10,D7="Low",5) and filled down to cover all names/marks. Then, to know the average simply use AVERAGE() function.

The second option is to compute the average directly, without the need of the support number column, as follows:

The formulation would be: =AVERAGE((COUNTIF(D7:D9,"High")*15),(COUNTIF(D7:D9,"Mid")*10),(COUNTIF(D7:D9,"Low")*5))

Martin

Answer 2

Hi Martin,

Thank you! I have tried this and it has worked well for what I needed.

Answer 3

Very glad it was useful!