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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
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.
 

Alvey

New Member
Joined
Nov 1, 2011
Messages
2
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.
 

bmullen37

New Member
Joined
Nov 5, 2011
Messages
7
I believe I have the similar issue, but with excel 2010, and I have more than 5 items in my drop down.....
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,704
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,906
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,566
Members
414,078
Latest member
Frills

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
Top