Numeric values for letter grades.

[Andy0311]

I need help on a formula to convert letter grades to numeric values. The grades are posted as:
A = 4
B = 3
C = 2
D = 1
F = 0
Once I get the range of numbers, I can easily average them. Thank you.

 

[dreid1011]

Perhaps this:

=LOOKUP(A2,{"A","B","C","D","F"},{4,3,2,1,0})

Edit: Whoops, forgot the "".

 

[Rick Rothstein]

This would also work...

=SEARCH(A1,"FDCBA")-1

and assuming upper case letters only, this would work too...

=MAX(0,69-CODE(A2))

 

[Andy0311]

Perhaps this:

=LOOKUP(A2,{"A","B","C","D","F"},{4,3,2,1,0})

Edit: Whoops, forgot the "".

Hello, Thanks for your help. With your formula I am getting in certain cells an #NA instead of a number. Am I doing something wrong? Thanks again.

B	3
A	4
B	3
B	#N/A
A	4
A	4
B	#N/A
A	#N/A
B	#N/A

 

[Rick Rothstein]

Check to make sure you do not have a space character in the cells along with the letters... your grade cells must only contain the single letter and nothing else.

 

[Andy0311]

This would also work...

=SEARCH(A1,"FDCBA")-1

and assuming upper case letters only, this would work too...

=MAX(0,69-CODE(A2))

Hello, Rick, and thanks for your help. When I apply the MAX formula in certain cells I am getting number 37 returned. Any reason this might be occurring? Thanks again.

B	3
A	4
B	3
B	37
A	4
A	4
B	37
A	37
B	37
C	2
D	1
A	4

 

[Andy0311]

Check to make sure you do not have a space character in the cells along with the letters... your grade cells must only contain the single letter and nothing else.

Thank you. I'll test it now.

 

[Rick Rothstein]

Your cell has a leading space character. If your method of putting the grades in the cells automatically does this, then try these versions of my formulas...

=SEARCH(TRIM(A1),"FDCBA")-1

=MAX(0,69-CODE(TRIM(A2)))

and you should be able to change @dreid1011's to this to make it work...

=LOOKUP(TRIM(A2),{"A","B","C","D","F"},{4,3,2,1,0})

 

[Andy0311]

Your cell has a leading space character. If your method of putting the grades in the cells automatically does this, then try these versions of my formulas...

=SEARCH(TRIM(A1),"FDCBA")-1

=MAX(0,69-CODE(TRIM(A2)))

and you should be able to change @dreid1011's to this to make it work...

=LOOKUP(TRIM(A2),{"A","B","C","D","F"},{4,3,2,1,0})

Worked perfectly! Thank you, sir!