Drop down list-->words=numbers?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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" :)
 
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
 
Thank You So Much!

Bernie Deitrick said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtered drop down list 2
How to get drop-down list to display text and #s but insert only # 1
How can I display text and #s in a drop-down list, but insert only 4
creating a graph from a drop down menu 2
Use word to associate an item in validation drop down list 2
Drop Down Lists 2
Drop Down List 2
Drop Down List 3

Back
Top