conditional if/then for letters to numbers

misseeoz

New Member
Joined
Mar 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:
QuestionLetter ScoreNumber Score
1​
A5
2 (Reversed)​
A1
3​
B4
4​
C3
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
Joined
Oct 24, 2012
Messages
4,661
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

misseeoz

New Member
Joined
Mar 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows

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
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top