# conditional if/then for letters to numbers

#### misseeoz

##### New Member
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.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### etaf

##### Well-known Member
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

##### Well-known Member
Here's a way that works, but how are we to know from the question if it should be forward or reverse?
Book1
BC
2A5
3B4
4C3
5D2
6E1
Sheet3
Cell Formulas
RangeFormula
C2:C6C2=70-CODE(UPPER(B2))

#### jasonb75

##### Well-known Member
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
BC
2A5
3B4
4C3
5D2
6E1
7a1
8b2
9c3
10d4
11e5
Sheet3
Cell Formulas
RangeFormula
C2:C11C2=IF(EXACT(B2,UPPER(B2)),70-CODE(B2),CODE(B2)-96)

#### misseeoz

##### New Member

ADVERTISEMENT

Here's a way that works, but how are we to know from the question if it should be forward or reverse?
Book1
BC
2A5
3B4
4C3
5D2
6E1
Sheet3
Cell Formulas
RangeFormula
C2:C6C2=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

##### Well-known Member
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

##### New Member
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!

Replies
6
Views
155
Replies
10
Views
177
Replies
19
Views
387
Replies
5
Views
60
Replies
1
Views
261

Threads
1,129,481
Messages
5,636,588
Members
416,927
Latest member
BNM8V6

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back