How to assign points to answers selected thru drop down on form

Hello, I created a form to be used during a training exam. Each cell has a drop with 3 options, Proficient, Needs Improvement, Not covered. Is it possible to assign each option a point value ( 3 - proficient, 2 Needs Improvement, 1 not covered) and have a total calculated once completed?

Thank you

Find more posts tagged with

Forms

Formulas

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @BenD27

You could add a helper column to convert the choice to point.😀

Example:

=IF(Choice@row = "Proficient", 3, IF(Choice@row = "Needs Improvement", 2, IF(Choice@row = "Not covered", 1)))

Then, use the SUM to add up.

Example:

=SUM(Point:Point)

https://app.smartsheet.com/b/publish?EQBCT=5d45ee360f0c4a7f87e91193b4e58d2e

BenD27

Thank you, does it matter that in the form I've created the columns are the questions not the rows? Each row will be a different learners name. In the image, test,test would be the name, then the scored columns follow. Where would I place the formula column in this set up?

BenD27

@jmyzk_cloudsmart_jp any thoughts on the above question?

Thank you very much for your help.

jmyzk_cloudsmart_jp

@BenD27 try something like this.

=COUNTIF([Choice 1]@row:[Choice 10]@row, "Not covered") * 1 + COUNTIF([Choice 1]@row:[Choice 10]@row, "Needs Improvement") * 2 + COUNTIF([Choice 1]@row:[Choice 10]@row, "Proficient") * 3

https://app.smartsheet.com/b/publish?EQBCT=c428b027bd2742eda9b2ad65389eaf4c

ticklilypad

Achieve this using Google Forms in combination with Google Sheets. Use Data Validation in Google Sheets to create dropdowns and assign point values using IF or VLOOKUP formulas to calculate totals based on selected options.

unknown

This content has been removed.

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.