conditional if/then for letters to numbers

[misseeoz]

Hopefully this hasn't been asked. If so, I'm apparently searching for the wrong phrasing.

I'm working with a scoring sheet that is a "circle the correct option" with letters A to E. Typically, A = 5, E = 1 on most, but there are a few reversed scored questions where A = 1, E = 5. I don't want to do a code where all cells say A = 5, because of this.
I was hoping to make a "Letter Score" cell that, when the letter is entered, the correct number value pops up next to in a "Number Score" cell, like so:

Question	Letter Score	Number Score
1​	A	5
2 (Reversed)​	A	1
3​	B	4
4​	C	3 ​

I tried to do an IF function with an OR that let me put "A" or "a", but it said I had too many conditions after I'd typed out all of the letter/number combos.
Does anyone have a better method of formatting this? I hope the way I'm explaining this isn't too convoluted.

Please help, I'm so confused.

 

[etaf]

usually an IF with a or A will work, not sure it needs to be tested for both - OR you could UPPER(cell) = "A"
OR a lookup table to lookup the Value to use
Index/match or vlookup
then its just the one formula

But how will excel know to use a 1 or 5 for A ?

 

[jasonb75]

Here's a way that works, but how are we to know from the question if it should be forward or reverse?

Book1
	B	C
2	A	5
3	B	4
4	C	3
5	D	2
6	E	1
Sheet3

Cell Formulas
Range	Formula
C2:C6	C2	=70-CODE(UPPER(B2))

 

[jasonb75]

I tried to do an IF function with an OR that let me put "A" or "a"

Using that as the decider, how about

Book1
	B	C
2	A	5
3	B	4
4	C	3
5	D	2
6	E	1
7	a	1
8	b	2
9	c	3
10	d	4
11	e	5
Sheet3

Cell Formulas
Range	Formula
C2:C11	C2	=IF(EXACT(B2,UPPER(B2)),70-CODE(B2),CODE(B2)-96)

 

[misseeoz]

Here's a way that works, but how are we to know from the question if it should be forward or reverse?

Book1
	B	C
2	A	5
3	B	4
4	C	3
5	D	2
6	E	1
Sheet3

Cell Formulas
Range	Formula
C2:C6	C2	=70-CODE(UPPER(B2))

usually an IF with a or A will work, not sure it needs to be tested for both - OR you could UPPER(cell) = "A"
OR a lookup table to lookup the Value to use
Index/match or vlookup
then its just the one formula

But how will excel know to use a 1 or 5 for A ?

I might not be doing a good job of explaining, I apologize.
I'm not sure how to answer your questions about knowing if it's forward or reversed. Do you mean, how do I know if the questions is supposed to be reversed scored or not?

 

[jasonb75]

Do you mean, how do I know if the questions is supposed to be reversed scored or not?

Yes, that is what we meant. I did post a second reply with an alternative idea that you may have missed as it was just before you replied.

 

[misseeoz]

Yes, that is what we meant. I did post a second reply with an alternative idea that you may have missed as it was just before you replied.

I did post before I saw your response. I'll try that. Thank you!