Map strings to represent numbers when used in functions

[RodrigoMartins]

I have some exams’ grades, A, B, C, D, F, I, O, in several cells, and I want to give numeric values to them (A=4, B=3, C=2, D=1, F=I=O=0) in order to calculate the mean value of them. I don’t want to actually replace them, I want to keep them showing letters, but when I use some function that uses that cell, they will use the numeric value associated/mapped for that letter.

Is this possible? The first thing that came to my mind was creating another sheet in the same document and use some function to convert the letters to numbers in cells in the second sheet and someway linking the values of that cells to the ones with letters, but I don’t know if it can be done.

[karolus]

you cannot directly do numeric Calculations, but you can setup some referenzrange with
O | I | F | D | C | B | A in exactly this order!

and calculate for Example the Averagevalue with

=AVERAGE(MATCH(A1:A100;referenzrange;0)-1)

as Matrix/Array-formula entered with ctrl+shift+enter respectivly the [x]Matrix-option in Formulawizard

[RodrigoMartins]

I didn’t understand how to implement your solution. How do I setup the reference range?

[RodrigoMartins]

I managed to do the conversion using IF conditionals:

=IF(G20="A",4,IF(G20="B",3,IF(G20="C",2,IF(G20="D",1,0))))

And then pointing my functions to the cells which have the result of the conversion.

[Lupp]

Suppose you have the grades in column A from row 11 to row 60 (range $A$11:$A$60).
To get the numerical values you want to assign to these silly grades you may use =(FIND($A11,"FIODCBA")-3)*IF(CURRENT()<0,0,1) or =(69-CODE(A11))*IF(CURRENT()<1;0;1) in row 11.
To get the average over all the 50 grades you may use an array formula based on one of the above formulae: {=AVERAGE((FIND($A11:$A60,"FIODCBA")-3)*IF(CURRENT()<0,0,1))} e.g. Array formulae are to be entered with Ctrl+Shift+Enter.

For creating and using a grading system containing three different representations for “zero points” (and this without a reasonable order) the only just grading should be a “FIO–”.