Forum Discussion
Help for the beginner please - converting text to a value
Hi, Just starting out so this may be easy. If a cell contains text and I want to convert the text phrase to a number, how do I do that? Here is the table:
If the phrase in column E is "Moderately-to-severely impaired", I want to convert the corresponding value in column C to 0.1. IF the phrase in column E is "Severely impaired" then I want the corresponding value in column C to be 0.9. Thank you.
5 Replies
Add a formula in Column B or C: =if(E2="Moderately...",0.1,if(E2="Severely...",0.9,0))
No, please don't. A nested IF is definitely NOT the way to go.
Why?
Well, the formula is really hard to write and harder to maintain.
If the parameters change and "Moderately" now needs to return "0.125" instead of "0.1", then you have to find EACH formula with the nested IFs and you need to change EACH formula accordingly. That is very poor data architecture.
With a lookup table and a Vlookup function, you only need to change ONE cell if the parameters for "Moderately" change. One cell. And all the Vlookup formulas will return the correct result.
Nested IFs are NOT a good way to solve this issue.
I think I did not understand the question. If you only have two conditions: the nested IF is a very effective and simple solution. if you have more values and you need to nest more IF statements... I agree IF is not a good solution. However your reasons are not very valid: You always can use a sheet to store your "Parameters" and use for example A$2$ to store your value, to later use it in the formula.
If the users are not very experienced, they may break the vlookup formula too (specially if they do not understand what it is doing). In the parameters approach all you have to do is change your parameters in the parameters sheet and you are good to go. If you have many conditions even in the vlookup solution, using a parameters shhet is a better alternative. Since we are dealing with not advance users. Maybe you should include how to create a distinct list of your options, why do you use $$ in the range in the vlookup function, etc.
Hello,
You can build a lookup table with all the different categories and their corresponding values. Then you can us a Vlookup() function to look up the value. Consider this screenshot:
The formula in cell C2 is =VLOOKUP(E2,$L$1:$M$6,2,FALSE) and copied down. I put in some dummy numbers in the lookup table, which sits in column L and M. The lookup table can be on a different sheet, of course.
