# Converting text to a numerical value

Options

I suspect there is a very simple solution to this. My knowledge of formulas is minimal and I want to make sure I am setting things up to work as I want them to work. In the example below what I want to do is to assign a numeric value to each of the text responses. (Very low = 1, low = 2, etc.). I would do this in both emotion and entanglement component columns. Then I want to take the numeric values and add them together to get the complexity score. I would appreciate any help.

Thank you,

Mary Pat

Tags:

• ✭✭✭✭✭
Options

The formula below should work, you can go in there and change some of the numbers around if you don't want a 1-5 scale, I just continued what you'd started.

Once the formula is working, you can right click it and set it to be a column formula if you want it to apply to the entire column.

```=IF([Emotional Component]@row="Very Low",1,IF([Emotional Component]@row="Low",2,IF([Emotional Component]@row="Moderate",3,IF([Emotional Component]@row="High",4,IF([Emotional Component]@row="Very High",5,"")
```
Options

Hi Peter,

Thank you so much. I greatly appreciate the help. What I learned is that when I want to convert the text to a score I have to do it in a separate column. I'll be able to hide that column and use that to create an overall complexity score.

Awesome!

Mary Pat

• ✭✭✭✭✭
Options

The formula below should work, you can go in there and change some of the numbers around if you don't want a 1-5 scale, I just continued what you'd started.

Once the formula is working, you can right click it and set it to be a column formula if you want it to apply to the entire column.

```=IF([Emotional Component]@row="Very Low",1,IF([Emotional Component]@row="Low",2,IF([Emotional Component]@row="Moderate",3,IF([Emotional Component]@row="High",4,IF([Emotional Component]@row="Very High",5,"")
```
Options

Hi Peter,

Thank you so much. I greatly appreciate the help. What I learned is that when I want to convert the text to a score I have to do it in a separate column. I'll be able to hide that column and use that to create an overall complexity score.

Awesome!

Mary Pat

• ✭✭✭✭✭
Options

With these 'ranked ratings' type dropdown options, I often add their relative numeric value to the front of their text, as this assists with natural ordering in reports or dashboard charts - otherwise you can get situations like "Very Low" and "Very High" are sorted together below "High" and "Low".

Try for example:

1 Very Low; 2 Low; 3 Moderate; 4 High; 5 Very High

Then you can use the function =VALUE(LEFT([Emotional Component]@row, 1)) to get the numeric values of 1 to 5 in your scoring formula.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!