eossma
asked on
MS Excel 2007 Vlookup to convert text to numbers
I have a Excel spreadsheet of survey results with roughly 100 record results. I have several array of questions/answers in one spreadsheet, so I'm thinking the VLOOKUP is my best approach; however I don't know how to set it up. I will focus this though on the first question asked on the survey.
The first question results are in F - O. The range of answers are :
Not Interested
Not Now
Not Sure
Somewhat Interested
Very Interested
I would like to have these converted to numbers:
Not Interested = 1
Not Now = 2
Not Sure = 3
Somewhat Interested = 4
Very Interested = 5
How would I go about this?
The first question results are in F - O. The range of answers are :
Not Interested
Not Now
Not Sure
Somewhat Interested
Very Interested
I would like to have these converted to numbers:
Not Interested = 1
Not Now = 2
Not Sure = 3
Somewhat Interested = 4
Very Interested = 5
How would I go about this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I mean O2 on all IFs.
Yeah! That ought to work! Feel free to upload the sheet if you have any problems and I can take a look for you.
Hope it goes well!
Hope it goes well!
ASKER
I get a #VALUE! error with:
=IF(F2:O2="Not Interested",1,IF(F2:O2="No
=IF(F2:O2="Not Interested",1,IF(F2:O2="No
Oh, sorry, I didn't look closely on the formula--the IF statement can't have a range (F2:O2). That's why I suggested helper columns-- P - Y.
If you wanted all the data consolidated into one column, I'd need to take a look at the setup. Can you upload a sample?
If you wanted all the data consolidated into one column, I'd need to take a look at the setup. Can you upload a sample?
ASKER
I'm attaching a modified version of the spreadsheet without names, so now the formula column sits in "O".
Sample did not upload
ASKER
attached.
Still not attched
ASKER
attached.
Survey.xlsx
Survey.xlsx
OK, you'd need a helper column for each column that has data. See attached.
Column O corresponds with Column E, Column P with Column F, etc.
Survey2.xlsx
Column O corresponds with Column E, Column P with Column F, etc.
Survey2.xlsx
ASKER
Thank you!
ASKER
=IF(F2="Not Interested",1,IF(F2:O2="No