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).

Tags:

Comments

  • Travis
    Travis Employee
    edited 02/16/16

    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)

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    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!

     

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

This discussion has been closed.