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
Best Answer
-
You would string a series of COUNTIFS together to add them up.
=COUNTIFS([1st Question]@row:[Last Question]@row, @cell = "Excellent") + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Very Good") * 2) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Good") * 3) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Fair") * 4) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Poor") * 5)
Answers
-
You would string a series of COUNTIFS together to add them up.
=COUNTIFS([1st Question]@row:[Last Question]@row, @cell = "Excellent") + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Very Good") * 2) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Good") * 3) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Fair") * 4) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Poor") * 5)
-
@Paul Newcome - I was looking for this formula and it worked perfectly! Thank you very much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!