Scoring Formulas?

I need help coming up with a formula that assigns a numeric value to a particular response in a cell of drop down options. Multiple options can be selected. For example, a cell drop down has four values to select from: 1A, 1B, 1C, 1D. Each selection is assigned a value of three points. So, if 1B and 1C are selected, the total value of the cell is 6 (3 for each). If only 1D is selected, then the cell has a value of 3. If 1A, 1C, 1D are selected, then the cell has a value of 9 and so on.

Any tips or advice is appreciated!

Find more posts tagged with

Accepted answers

All comments

Protonsponge

Hello @LVU22,

One option that might work for you is something like the formula below:-

=IF(ISBLANK(Options@row), "", IF(HAS(Options@row, "1A"), 3, 0) + IF(HAS(Options@row, "1B"), 3, 0) + IF(HAS(Options@row, "1C"), 3, 0) + IF(HAS(Options@row, "1D"), 3, 0))

If you wanted to have different scores for options 1A, 1B, 1C, 1D you would just change the 3 in the general formula as highlighted below:

IF(HAS(Options@row, "1A"), 3, 0)

I made a quick demo below and all seems to be working

I hope that is helpful to you in some way,

Protonsponge

If you will always assign 3 points to each option, a much simpler alternative would be to use the COUNTM function to count the number of options selected in your [Options] column and multiple by 3 as shown below.

=COUNTM(Options@row) * 3

Protonsponge

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.