How can I assign a number to a string?

[Guest]

Hi,

I'm a total newb at Excel and doing a risk assessment for my company.

I have three risk values, 0.1, 0.5 and 1.0

I would like to assign these values to the words Low, Medium and High,

Or better yet, the strings

Low (0.1), Medium (0.5), High (1.0)

Basically, I just want these words in a cell to reall mean a number

So in my spreadsheet, I can have a column with the words

Low, Medium or High and that column can be multiplied by another number
column.

Have googled all over for this and searched help, but can't find anything
quite this simple that I can still understand. Can anyone tell me how to do
this?

While I can get buy using two columns for now and cutting and pasting etc...
this is an ongoing project so I'd like to get this to work so when I change a
value from medium to low, the final risk value will change.

Thanks

 

[JulieD]

Hi

one method would be just to put the words in and then when you need to use
them in calculations do the "value" bit then.

e.g.
=COUNTIF(A1:A10,"Low") *.1
will count the number of times that the word Low appears in the range A1:A10
and multiply that by .1

or
=IF(ISBLANK(A1),0,IF(A1="Low",.1,IF(A1="Medium",.5,1)))

or with another table containing the text in Sheet2!A2:A4 and the associated
values in Sheet2!B2:B4
- to substitute the value for the word in A1 use
=VLOOKUP(A1,Sheet2!A2:B4,2,0)

all of the above can be embedded into other calculations

Hope this helps
Cheers
JulieD

 

[Guest]

Thanks Julie, the second one does exactly what I need, couldn't quite get my
head around the first one, the third one was the line of though that I was
already on, but in the end, had trouble with that as well, but then I looked
closer at the second one and that works perfect and I think I can figure out
how to do the rest from there on in using IF as it gets more complicated but
I can just keep nesting them.

Thanks again

Hi

one method would be just to put the words in and then when you need to use
them in calculations do the "value" bit then.

e.g.
=COUNTIF(A1:A10,"Low") *.1
will count the number of times that the word Low appears in the range A1:A10
and multiply that by .1

or
=IF(ISBLANK(A1),0,IF(A1="Low",.1,IF(A1="Medium",.5,1)))

or with another table containing the text in Sheet2!A2:A4 and the associated
values in Sheet2!B2:B4
- to substitute the value for the word in A1 use
=VLOOKUP(A1,Sheet2!A2:B4,2,0)

all of the above can be embedded into other calculations

Hope this helps
Cheers
JulieD

 

[JulieD]

Hi Jog

glad to assist. However, just a note - you can only nest to 7 levels in an
IF statement. If you need more than that generally the best solution is
then #3.

Cheers
JulieD

Thanks Julie, the second one does exactly what I need, couldn't quite get
my
head around the first one, the third one was the line of though that I was
already on, but in the end, had trouble with that as well, but then I
looked
closer at the second one and that works perfect and I think I can figure
out
how to do the rest from there on in using IF as it gets more complicated
but
I can just keep nesting them.

Thanks again