Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Feature request: Weighted options in dropdown list
Hi,
When sending out web forms with dropdown lists it would be great to be able to connect each option to a number. That way we can run avarages and calculations on the results. Fully agree (=5), Somewhat agree (=3), Do not agree (=1).
Comments
-
+1 for that feature..
-
This is possible! I actually do this exact thing when I send out survey emails and track the results.
To do this, you will need to build a nested IF statement that counts the number of times one of the options appear in the column (when people select that option) then multiply it by the value. Do this for each option the divide by the number of responses.
Here's an example.
The possible choices for users to select from the Dropdown are:
5 - Very High Quality
4 - High Quality
3 - Neutral
2 - Low Quality
1 - Very Low Quality
Here is the formula:
=((COUNTIF(Quality5:Quality50, "5 - Very High Quality") * 5) + (COUNTIF(Quality5:Quality50, "4 - High Quality") * 4) + (COUNTIF(Quality5:Quality50, "3 - Neutral") * 3) + (COUNTIF(Quality5:Quality50, "2 - Low Quality") * 2) + (COUNTIF(Quality5:Quality50, "1 - Very Low Quality") * 1)) / COUNT(Quality5:Quality50)
-
I do something similar to Travis, except I like extra columns so I determine the value with the nest IF in the column.
Elsewhere, I put my counters (how many of each), mean (average), totals.
Craig
-
Thank you both for your suggestions. I actually will be doing the repporting in MS excel for this particular project, the responses needs to be imported to an existing excel template, and can use find/replace after I´ve exported the sheet.
-
Hello,
I'm trying to accomplish the same as discussed above for a survey. I am not able to make the formula work. Using:
=((COUNTIF([QuestionABC]1:[QuestionDEF]1), "Strongly Agree") * 4) + ((COUNTIF([QuestionABC]1:[QuestionDEF]1), "Agree") * 3)
The formula seems fine up until I want to add the second scoring option for "agree".
Any ideas what may be wrong with it? Thanks in advance!
-
Hi Summer,
You've probably already solved this.
Try this.
=COUNTIF(QuestionABC@row:QuestionDEF@row; "Strongly Agree") * 4 + COUNTIF(QuestionABC@row:QuestionDEF@row; "Agree") * 3
The same version but with the below changes for your and others convenience.
=COUNTIF(QuestionABC@row:QuestionDEF@row, "Strongly Agree") * 4 + COUNTIF(QuestionABC@row:QuestionDEF@row, "Agree") * 3
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
