Converting text to a numerical value

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:

Best Answers

  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    edited 10/21/21 Answer ✓

    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
    Mary Pat Aust ✭✭✭
    Answer ✓

    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

Answers

  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    edited 10/21/21 Answer ✓

    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
    Mary Pat Aust ✭✭✭
    Answer ✓

    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
    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.

    Adrian Mandile
    CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
    Collaborative | Holistic | Effective | Systems | Solutions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!