Ask Your Question
0

Map strings to represent numbers when used in functions

asked 2015-04-25 02:34:59 +0200

Rodrigo Martins gravatar image

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.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2015-04-27 17:00:46 +0200

Rodrigo Martins gravatar image

updated 2015-04-27 17:01:26 +0200

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.

edit flag offensive delete link more
0

answered 2015-04-25 08:38:00 +0200

karolus gravatar image

updated 2015-04-25 08:40:46 +0200

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

edit flag offensive delete link more

Comments

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

Rodrigo Martins gravatar imageRodrigo Martins ( 2015-04-27 16:57:46 +0200 )edit
0

answered 2015-04-27 22:09:05 +0200

Lupp gravatar image

updated 2015-04-27 22:18:41 +0200

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-04-25 02:34:59 +0200

Seen: 462 times

Last updated: Apr 27 '15