Drop down list-->words=numbers?

Hello!

I have a quick question. If I create a drop down list in excel, can I tell
other cells to read certain words as numbers. For example. if I have a drop
down list with responses "I strongly agree", "I agree", etc. could I make
those words have a value? Srongly agree would equal "5" and then excel could
add up the responses and divide by the number of questions for an average
response? I know it would be easier to just type in 5, but I think this is
easier.

Is this, in any way, possible?

Thank you for your time

Hello no-name,

Say that your two cells are A2 and B2. In cell B2, use the formula

=IF(A2="","",MATCH(A2,{"I strongly disagree","I disagree","Neutral", "I agree","I strongly
agree"},FALSE))

If you have a list of possible values, sorted 1 to 5, name it Agreements - then you could use the
formula

=IF(A2="","",MATCH(A2,Agreements,FALSE))

And you could also use the source =Arguments as your data validation list.

HTH,
Bernie
MS Excel MVP


"Anonymous" <[email protected]> wrote in message
news:[email protected]...
> Hello!
>
> I have a quick question. If I create a drop down list in excel, can I tell
> other cells to read certain words as numbers. For example. if I have a drop
> down list with responses "I strongly agree", "I agree", etc. could I make
> those words have a value? Srongly agree would equal "5" and then excel could
> add up the responses and divide by the number of questions for an average
> response? I know it would be easier to just type in 5, but I think this is
> easier.
>
> Is this, in any way, possible?
>
> Thank you for your time

the combo box from the forms toolbar has a number in the "linked cell" the
first choice from the combobox will compute as 1 in the linked cell the fifth
choice is 5
--
paul
remove nospam for email addy!



"Bernie Deitrick" wrote:

> Hello no-name,
>
> Say that your two cells are A2 and B2. In cell B2, use the formula
>
> =IF(A2="","",MATCH(A2,{"I strongly disagree","I disagree","Neutral", "I agree","I strongly
> agree"},FALSE))
>
> If you have a list of possible values, sorted 1 to 5, name it Agreements - then you could use the
> formula
>
> =IF(A2="","",MATCH(A2,Agreements,FALSE))
>
> And you could also use the source =Arguments as your data validation list.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Anonymous" <[email protected]> wrote in message
> news:[email protected]...
> > Hello!
> >
> > I have a quick question. If I create a drop down list in excel, can I tell
> > other cells to read certain words as numbers. For example. if I have a drop
> > down list with responses "I strongly agree", "I agree", etc. could I make
> > those words have a value? Srongly agree would equal "5" and then excel could
> > add up the responses and divide by the number of questions for an average
> > response? I know it would be easier to just type in 5, but I think this is
> > easier.
> >
> > Is this, in any way, possible?
> >
> > Thank you for your time
>
>
>

Is there any way to make strongly disagree equal 0? Also, I'm not getting the
last part of your post. How can I name the list?

Thanks,
"no-name"

"Bernie Deitrick" wrote:

> Hello no-name,
>
> Say that your two cells are A2 and B2. In cell B2, use the formula
>
> =IF(A2="","",MATCH(A2,{"I strongly disagree","I disagree","Neutral", "I agree","I strongly
> agree"},FALSE))
>
> If you have a list of possible values, sorted 1 to 5, name it Agreements - then you could use the
> formula
>
> =IF(A2="","",MATCH(A2,Agreements,FALSE))
>
> And you could also use the source =Arguments as your data validation list.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Anonymous" <[email protected]> wrote in message
> news:[email protected]...
> > Hello!
> >
> > I have a quick question. If I create a drop down list in excel, can I tell
> > other cells to read certain words as numbers. For example. if I have a drop
> > down list with responses "I strongly agree", "I agree", etc. could I make
> > those words have a value? Srongly agree would equal "5" and then excel could
> > add up the responses and divide by the number of questions for an average
> > response? I know it would be easier to just type in 5, but I think this is
> > easier.
> >
> > Is this, in any way, possible?
> >
> > Thank you for your time
>
>
>

Hey No-Name,

If you want 0,2,3,4,5, use something like

=IF(A2="","",MATCH(A2,{"I strongly disagree","This cannot be matched","I
disagree","Neutral", "I agree","I strongly agree"},FALSE)-1)

If you want 0,1,2,3,4 use something like

=IF(A2="","",MATCH(A2,{"I strongly disagree","I disagree","Neutral", "I
agree","I strongly agree"},FALSE)-1)

To name a range, use the Insert / Name dialog.

HTH,
Bernie
MS Excel MVP

"Anonymous" <[email protected]> wrote in message
news:[email protected]...
> Is there any way to make strongly disagree equal 0? Also, I'm not getting
> the
> last part of your post. How can I name the list?
>
> Thanks,
> "no-name"
>
> "Bernie Deitrick" wrote:
>
>> Hello no-name,
>>
>> Say that your two cells are A2 and B2. In cell B2, use the formula
>>
>> =IF(A2="","",MATCH(A2,{"I strongly disagree","I disagree","Neutral", "I
>> agree","I strongly
>> agree"},FALSE))
>>
>> If you have a list of possible values, sorted 1 to 5, name it
>> Agreements - then you could use the
>> formula
>>
>> =IF(A2="","",MATCH(A2,Agreements,FALSE))
>>
>> And you could also use the source =Arguments as your data validation
>> list.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Anonymous" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello!
>> >
>> > I have a quick question. If I create a drop down list in excel, can I
>> > tell
>> > other cells to read certain words as numbers. For example. if I have a
>> > drop
>> > down list with responses "I strongly agree", "I agree", etc. could I
>> > make
>> > those words have a value? Srongly agree would equal "5" and then excel
>> > could
>> > add up the responses and divide by the number of questions for an
>> > average
>> > response? I know it would be easier to just type in 5, but I think this
>> > is
>> > easier.
>> >
>> > Is this, in any way, possible?
>> >
>> > Thank you for your time
>>
>>
>>

Thank You So Much!

"Bernie Deitrick" wrote:

> Hey No-Name,
>
> If you want 0,2,3,4,5, use something like
>
> =IF(A2="","",MATCH(A2,{"I strongly disagree","This cannot be matched","I
> disagree","Neutral", "I agree","I strongly agree"},FALSE)-1)
>
> If you want 0,1,2,3,4 use something like
>
> =IF(A2="","",MATCH(A2,{"I strongly disagree","I disagree","Neutral", "I
> agree","I strongly agree"},FALSE)-1)
>
> To name a range, use the Insert / Name dialog.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Anonymous" <[email protected]> wrote in message
> news:[email protected]...
> > Is there any way to make strongly disagree equal 0? Also, I'm not getting
> > the
> > last part of your post. How can I name the list?
> >
> > Thanks,
> > "no-name"
> >
> > "Bernie Deitrick" wrote:
> >
> >> Hello no-name,
> >>
> >> Say that your two cells are A2 and B2. In cell B2, use the formula
> >>
> >> =IF(A2="","",MATCH(A2,{"I strongly disagree","I disagree","Neutral", "I
> >> agree","I strongly
> >> agree"},FALSE))
> >>
> >> If you have a list of possible values, sorted 1 to 5, name it
> >> Agreements - then you could use the
> >> formula
> >>
> >> =IF(A2="","",MATCH(A2,Agreements,FALSE))
> >>
> >> And you could also use the source =Arguments as your data validation
> >> list.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Anonymous" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hello!
> >> >
> >> > I have a quick question. If I create a drop down list in excel, can I
> >> > tell
> >> > other cells to read certain words as numbers. For example. if I have a
> >> > drop
> >> > down list with responses "I strongly agree", "I agree", etc. could I
> >> > make
> >> > those words have a value? Srongly agree would equal "5" and then excel
> >> > could
> >> > add up the responses and divide by the number of questions for an
> >> > average
> >> > response? I know it would be easier to just type in 5, but I think this
> >> > is
> >> > easier.
> >> >
> >> > Is this, in any way, possible?
> >> >
> >> > Thank you for your time
> >>
> >>
> >>
>
>