converting letters to numeric values

wingingit2018

New Member
Joined
Feb 15, 2018
Messages
5
I have a likert scale survey project in front of me.
I need to take the survey answers and give them a numeric value.
I then need the numberic value to populate on a calculation sheet.
This survey will be used 100x over the next few months.

Sheet one:
1. Question: Answer:_______

The answer options will be A, B, C, D or E with a numeric value assigned to each answer option.
A = 1, B = .5, C = 0, D = -.5, E = -1

I need the numeric value to populate on Sheet two.
1. Answer Score: _______

for example:
(sheet one)
(cell A1) 1. Answer: A
(cell B1) 2. Answer: C

(sheet two)
(cell C5)1. Answer Score: 1
(cell C6)2. Answer Score: 0

How do I reference sheet one, cell A1's text - to populate into sheet 2, cell C5 as a numeric value?
I am using excel 2013.
I hope I asked this question clearly :?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
705
Platform
  1. Windows
wingingit2018, Good afternoon.

Very clear explanation. Congrats.

Try to use:

Sheet 2

C5 -->
Code:
 [B]=LOOKUP( Sheet1!A1, {"A","B","C","D","E"}, {1,0.5,0,-0.5,-1} )[/B]

Please, tell us if it worked as necessary.

I hope it helps.
 

wingingit2018

New Member
Joined
Feb 15, 2018
Messages
5
wingingit2018, Good afternoon.

Very clear explanation. Congrats.

Try to use:

Sheet 2

C5 -->
Code:
 [B]=LOOKUP( Sheet1!A1, {"A","B","C","D","E"}, {1,0.5,0,-0.5,-1} )[/B]

Please, tell us if it worked as necessary.

I hope it helps.



Thank you so much!
This is perfect
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,608
Members
412,330
Latest member
carlosjw
Top