NEED HELP! Cannot figure out complex function

[tiger]

I have a complex task and I am not sure if excel can do this or not.
This is really important so any help is very welcome.

I have given severel people a servay. Each question has seven possible
answers. They are: strongly agree, agree, agree somewhat, neutral,
disagree somewhat, disagree, strongly disagree. I need to write a
function in excel such that when ever I enter one of these answers a
specific numerical value from 1-7 apears. For example, Strongly
agree=1, agree=2, agree somewhat =3 etc. . . . strongly disagree=7. So,
if a person answered agree somewhat then when I paste this answer into
the correct cell then 3 pops up as the value in that cell. Is this
possible? If so what is the function, formula, or array that I need to
use to do this?

I have figured out to do an if statement for two choices, but can a
statement be created that chooses between multiple choices?

any help anyone can give would be great
thanks,
tiger

 

[Rowan]

7 options you can do with nested if statements as follows:

=IF(A1="strongly agree",1,IF(A1="Agree",2,IF(A1="agree
somewhat",3,IF(A1="neutral",4,IF(A1="disagree
somewhat",5,IF(A1="disagree",6,IF(A1="strongly disagree",7,"")))))))

if you want to have more options you should set up a refrence table and use
the Vlookup function, see:

http://www.contextures.com/xlFunctions02.html

hope this helps
Rowan

 

[Rowan]

PS if you don't want the formula to be case sensitive then:

=IF(UPPER(A1)="STRONGLY AGREE",1,IF(UPPER(A1)="AGREE",2,IF(UPPER(A1)="AGREE
SOMEWHAT",3,IF(UPPER(A1)="NEUTRAL",4,IF(UPPER(A1)="DISAGREE
SOMEWHAT",5,IF(UPPER(A1)="DISAGREE",6,IF(UPPER(A1)="STRONGLY
DISAGREE",7,"")))))))

Regards
Rowan

 

[Aladin Akyurek]

Try to set up a lookup formula, e.g., with the VLOOKUP function.

 

[Michael]

Hi Tiger
You could also use the CHOOSE function.
=CHOOSE(A1,"Strongly agree","agree","somewhat agree","neutral","disagree
somewhat","disagree","Strongly disagree")
This function will give you up to 29 options.

HTH
Michael Mitchelson

 

[Biff]

Hi!

Think you have it backwards.

The OP wants to return a number that corresponds to a phrase not return a
phrase that corresponds to a number.

But it's good to see someone's thinking about CHOOSE!

Biff

 

[Michael]

Hi Biff
Yeah, you're right.
I suppose the OP could type the text in A1 and still use CHOOSE
=CHOOSE(A1,1,2,3,4,5,6,7)
How's that for wriggling out of stupidity.

 

[Ron Rosenfeld]

I have a complex task and I am not sure if excel can do this or not.
This is really important so any help is very welcome.

I have given severel people a servay. Each question has seven possible
answers. They are: strongly agree, agree, agree somewhat, neutral,
disagree somewhat, disagree, strongly disagree. I need to write a
function in excel such that when ever I enter one of these answers a
specific numerical value from 1-7 apears. For example, Strongly
agree=1, agree=2, agree somewhat =3 etc. . . . strongly disagree=7. So,
if a person answered agree somewhat then when I paste this answer into
the correct cell then 3 pops up as the value in that cell. Is this
possible? If so what is the function, formula, or array that I need to
use to do this?

I have figured out to do an if statement for two choices, but can a
statement be created that chooses between multiple choices?

any help anyone can give would be great
thanks,
tiger

I would suggest using Data Validation with List for the Validation Criteria.

Set up your list in some range of cells; for example I1:I7

I1: Strongly agree
I2: Agree
..
..
I7: Strongly disagree.

Then select, for example, A1.
From the main menu; select

Data/Validation
Settings
Allow: List
Source: $I$1:$I$7

Check the Ignore Blank and in-cell dropdown boxes.

Then, for your formula, use:

=MATCH(A1,$I$1:$I$7,0)




--ron

 

[Ron Rosenfeld]

I have a complex task and I am not sure if excel can do this or not.
This is really important so any help is very welcome.

I have given severel people a servay. Each question has seven possible
answers. They are: strongly agree, agree, agree somewhat, neutral,
disagree somewhat, disagree, strongly disagree. I need to write a
function in excel such that when ever I enter one of these answers a
specific numerical value from 1-7 apears. For example, Strongly
agree=1, agree=2, agree somewhat =3 etc. . . . strongly disagree=7. So,
if a person answered agree somewhat then when I paste this answer into
the correct cell then 3 pops up as the value in that cell. Is this
possible? If so what is the function, formula, or array that I need to
use to do this?

I have figured out to do an if statement for two choices, but can a
statement be created that chooses between multiple choices?

any help anyone can give would be great
thanks,
tiger

To add to what I previously wrote, if you really need the number to pop up in
the SAME cell that you enter the survey response, you will need to use a VB
event drive macro. If that is really a requirement, post back with some more
detail.


--ron

 

[tiger]

Hello Ron,
thanks for the reply. I need the number to pop up into the same cell.
This would be ideal.
thanks,
tyra

 

[Ron Rosenfeld]

Hello Ron,
thanks for the reply. I need the number to pop up into the same cell.
This would be ideal.
thanks,
tyra

Here's one way.

Set up your worksheet as follows:

======================
I would suggest using Data Validation with List for the Validation Criteria.

Set up your list in some range of cells; for example I1:I7

Insert/Name/Define: Responses (Refers to: $I$1:$I$7) or where ever you put
the list of responses.

I1: Strongly agree
I2: Agree
..
..
I7: Strongly disagree.

Then select, for example, A1:A20 as the range in which you will place the
responses.
From the main menu; select

Data/Validation
Settings
Allow: List
Source: $I$1:$I$7

Check the Ignore Blank and in-cell dropdown boxes.
======================

Now right click on the sheet tab and select View Code from the right click
menu.

Paste the code below into the window that opens:

=========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Set aoi = Range("A1:A20")
Application.EnableEvents = False

If Not Intersect(Target, aoi) Is Nothing Then
If IsNumeric(Target) Then GoTo Done
Target.Value = Application.WorksheetFunction. _
Match(Target.Text, Range("Responses"), 0)
End If

Done: Application.EnableEvents = True
End Sub
===========================

Hopefully, this will give you enough of a start to be able to modify it to your
precise requirements.

Post back if you have any questions.


--ron