NEED HELP! Cannot figure out complex function

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

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

"tiger" wrote:

> 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
>
>

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

"Rowan" wrote:

> 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
>
> "tiger" wrote:
>
> > 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
> >
> >

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

tiger wrote:
> 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
>

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


"Aladin Akyurek" wrote:

> Try to set up a lookup formula, e.g., with the VLOOKUP function.
>
> tiger wrote:
> > 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
> >
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Aladin Akyurek" wrote:
>
>> Try to set up a lookup formula, e.g., with the VLOOKUP function.
>>
>> tiger wrote:
>> > 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
>> >
>>

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.
--
Michael Mitchelson


"Biff" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "Aladin Akyurek" wrote:
> >
> >> Try to set up a lookup formula, e.g., with the VLOOKUP function.
> >>
> >> tiger wrote:
> >> > 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
> >> >
> >>
>
>
>

On 14 Aug 2005 16:36:19 -0700, "tiger" <[email protected]> wrote:

>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

On 14 Aug 2005 16:36:19 -0700, "tiger" <[email protected]> wrote:

>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

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 wrote:
> On 14 Aug 2005 16:36:19 -0700, "tiger" <[email protected]> wrote:
>
> >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

On 15 Aug 2005 08:01:24 -0700, "tiger" <[email protected]> wrote:

>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