Custom Formating Letters to Numbers while Displaying Letters

[Luke Androsiglio]

I am trying to accomplish this in terms of letter grades corresponding
to number grades. How do I format a cell so that I can enter a letter
grade: A, A-, B+, B, etc. and the corresponding number: 4.00, 3.67,
3.33, 3.00, etc. to each letter grade will be the numeric value of the
cell (which I can reference in a formula in a different cell), while
the letter remains displayed in the cell?
As an example: in cell A1, I would type: B+, I would like B+ to remain
displayed, but the value of the cell would be 3.33. Therefore, I could
multiply cell B1 (which has a value of 3.00) to cell A1 and resulting
answer would be 9.99.

Thank you

 

[T. Valko]

How about creating a 2 column table that lists the letter grades in the left
column and the corresponding numeric value in the right column. Like this:

...........J..........K
1.......A..........4
2.......A-........3.67
3.......B+........3.33
4.......B..........3

Then:

A1 = some letter grade like B+
B1 = 3.00

C1 formula:

=B1*SUMIF(J1:J4,A1,K1:K4)

Result = 9.99

 

[Gord Dibben]

Example formula using a helper cell.

=IF(A1="","",LOOKUP(A1,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f"},{4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})*B1)

entered in C1


Gord Dibben MS Excel MVP