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

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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)

All comments

Paul Newcome

You would string a series of COUNTIFS together to add them up.

Clayton DSouza

@Paul Newcome - I was looking for this formula and it worked perfectly! Thank you very much

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.