How can I input letters in excel and have it recognize the letter as a number value? (grading scale)?

Question

I'd like to email the file to you if you can help, and perhaps you can enter the function it needs. Right now, I have it recognizing that 0=N, and 1=P-, and so on.... (these are grades we use for primary school).

I want to be able to enter CD, CD-, P+,P,P-, and N as grades and have excel recognize these letters as having a number value.

This way, I can enter grades, like: P, P, N, CD, P, P and based on the value assigned to each letter, it'll calculate the average being a P or so.

Anyone know how to do this?

Answer 1

I think I can help you with Excel functions and all related to Excel. I have sound knowledge in excel, I have made many programes in Excel and it is being used in our company. give me more details that you need.

Answer 2

There may be a fancier and shorter array formula to accomplish this, but this should work:

Say you enter the letter grades in cells A1:D1:

To get grand total:

(I've arbitrarily assigned values for each grade):

=COUNTIF(A1:D1,"CD")*5 +COUNTIF(A1:D1,"CD-")*4 +COUNTIF(A1:D1,"P+")*3 +COUNTIF(A1:D1,"P")*2 +COUNTIF(A1:D1,"P-")*1

To get the average:

=( COUNTIF(A1:D1,"CD")*5 +COUNTIF(A1:D1,"CD-")*4 +COUNTIF(A1:D1,"P+")*3 +COUNTIF(A1:D1,"P")*2 +COUNTIF(A1:D1,"P-")*1 ) /COUNTA(A1:D1)

To convert number to letter (rounds down), with number in cell F1:

=VLOOKUP(F1, {0,"N"; 1,"P-"; 2,"P"; 3,"P+"; 4,"CD-"; 5,"CD"}, 2, TRUE)