Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I assign a number to a string?
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 -- If a man speaks in a woods and there is no woman there to hear him... Is he still wrong? |
#2
|
|||
|
|||
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 "Jog Dial" wrote in message ... 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 -- If a man speaks in a woods and there is no woman there to hear him... Is he still wrong? |
#3
|
|||
|
|||
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 "JulieD" wrote: 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 "Jog Dial" wrote in message ... 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 -- If a man speaks in a woods and there is no woman there to hear him... Is he still wrong? |
#4
|
|||
|
|||
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 "Jog Dial" wrote in message ... 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 "JulieD" wrote: 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 "Jog Dial" wrote in message ... 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 -- If a man speaks in a woods and there is no woman there to hear him... Is he still wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
assign auto number and auto date | Excel Discussion (Misc queries) | |||
Number of Column Limitations | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
Count number of shaded cells | Excel Discussion (Misc queries) |