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
Best Answers
-
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,"")
-
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
-
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,"")
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!