Asigning a word a numerical value

Alvey

New Member
Joined
Nov 1, 2011
Messages
2
Could anyone tell me if it is possible to assign a numerical value to a given word in an excel 2003 spreadsheet. I have created a spreadsheet where there are 5 options that can be selected from a drop down menu in a column and I want to know if it is possible to give each of the 5 words a numerical value when selected. i.e. Low = 1, Medium = 2, etc etc.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
If you mean by “drop down menu” a list box control, then you need to set the
”cell link”. You can do this with:


  • Right click list box
  • Choose “Format Control”
  • Specify the “cell link” ie $A$10
  • Click OK

Then when an item is selected it’s numerical location will be in the specified cell

If it isn’t a list box control, give us more information.
 
Upvote 0
The drop down I have created is a simple 'Data Validation' drop down list from which I have assigned 5 possible outcomes. ideally what I am wanting to do is select any one of the 5 outcomes, each of which will have a numerical value assigned to it. So if I pick that outcome the representative numerical value will be applied. However, I dont want the numerical value to actually show. If that makes sense. I.e. so if I pick 'low' for example from the validation list a numerical value will be assigned to it that I can use to form simple calculations.

P.s Thanks for your help so far with this.
 
Upvote 0
I believe I have the similar issue, but with excel 2010, and I have more than 5 items in my drop down.....
 
Upvote 0
Can't you apply the "simple" calculations to the text value,eg,
Code:
Low =1
Med =2
High =3
=IF(A1="Med", b1*2,"")
I think this will be easier than trying to apply a numerical equivalent.

Maybe post the formula you would like to use in this instance
 
Upvote 0
Pehaps something like
=MATCH(A1, {"Low","Medium","High"}, 0)

If the source of the Validation list is a range, S1:S3 for example,
=MATCH(A1, S1:S3, 0)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top