What formula should I use to convert drop down text responses to a corresponding number?

We are working on an assessment survey that has text dropdown values. Each of the text responses will be scored with a different numerical value. I have created a corresponding column to capture the score for each of the questions but am having trouble with the formula. For example, the respondent will answer question 1 with one of the 5 options:

Excellent, Very Good, Good, Fair, Poor.

Excellent will be worth 1 point, Very Good 2 points, Good, 3 points, Fair, 4 points, and Poor 5 points.

Can I write one formula to capture the corresponding number in my score column?

Thank you in advance for your help!

Sara

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!