Converting text to a numerical value

Mary Pat Aust

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

Parker Oxford

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,"")

Mary Pat Aust

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

Parker Oxford

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,"")

Mary Pat Aust

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

Adrian Mandile CHESS

Hi @Mary Pat Aust,

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.