Based on location, convert survey responses to a numerical value in separate sheet

Amy Arnold
Amy Arnold ✭✭✭✭
edited 03/21/20 in Formulas and Functions

Hi,


I created a form to collect survey results, the responses are returned to Smartsheet #1 (image below). The survey has 18 questions with these dropdown (single select) responses:

  • Good
  • Satisfactory
  • Does Not Meet Standards
  • Not applicable


In Smartsheet #2, I want to search for responses for "Phoenix" and then convert the responses to these numerical values so I can create charts in a dashboard:

  • Good = 3
  • Satisfactory = 2
  • Does Not Meet Standards = 1
  • Not applicable = 0


I wrote this formula but it is returning an #UNPARSEABLE error, even with the Edit Column Properties set to Text/Number :


=INDEX({Feedback_Form Range 1}}, MATCH(COLLECT({Feedback_Form Range 2}, {Feedback_Form Range 1}, "Phoenix")), IF(OR({Feedback_Form Range 1} = Good, {Feedback_Form Range 1} = Satisfactory, {Feedback_Form Range 1} = Does Not Meet Standards, {Feedback_Form Range 1} = Not applicable), "3", "2", "1", "0"))


How would you write a formula to INDEX the site column and then return a numerical value for the response in that cell?



Thank you from a newbie :)

Best Answers

«1

Answers

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

    Hi Amy,

    At a quick glance I noticed an error in the formula. You have one too many } in the INDEX part. (See below)

    Try to remove that one first and see if that works.

    =INDEX({Feedback_Form Range 1}}, MATCH(COLLECT({Feedback_Form Range 2}, {Feedback_Form Range 1}, "Phoenix")), IF(OR({Feedback_Form Range 1} = Good, {Feedback_Form Range 1} = Satisfactory, {Feedback_Form Range 1} = Does Not Meet Standards, {Feedback_Form Range 1} = Not applicable), "3", "2", "1", "0"))

    Did that work?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Amy Arnold
    Amy Arnold ✭✭✭✭
    edited 03/23/20

    Hi Andrée,

    Thank you for the reply and well wishes. I'm staying safe and indoors.

    I removed the extra } and it still returns #UNPARSEABLE. Here's the updated formula:

    =INDEX({Feedback_Form Range 1}, MATCH(COLLECT({Feedback_Form Range 2}, {Feedback_Form Range 1}, "Phoenix")), IF(OR({Feedback_Form Range 1} = Good, {Feedback_Form Range 1} = Satisfactory, {Feedback_Form Range 1} = Does Not Meet MTC Standards, {Feedback_Form Range 1} = Not applicable), "3", "2", "1", "0"))

    Be safe and have a great day!

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

    Happy to help!

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What is your desired end goal for the data? You say you want to use this to create charts in a dashboard. Are you planning on showing (for example) Phoenix multiple times on the chart for multiple entries?

    It honestly may be easier to convert the results into numbers on the source sheet and then pull from there.


    To pull the data to duplicate the sheet, I would suggest using a helper column in the target sheet that will essentially replicate the row number.

    =COUNTIFS(Site$1:Site@row, OR(@cell = "", @cell <> ""))


    Then you could use a very basic series of INDEX functions using the row number instead of a MATCH function.

    =INDEX({Source Sheet Column to Pull}, [Row #]@row)


    You would just update the range for each of the appropriate columns.


    If your end goal is to consolidate the data and get total counts or averages and only have one entry for each site displayed, you can use cross sheet references and use basic additions to the formulas to get your numbers.

    =COUNTIFS({Source Sheet Q1 Column}, "Good", {Source Sheet Site Column}, "Phoenix") * 3

    =COUNTIFS({Source Sheet Q1 Column}, "Satisfactory", {Source Sheet Site Column}, "Phoenix") * 2

    =COUNTIFS({Source Sheet Q1 Column}, "Does Not Meet Standards", {Source Sheet Site Column}, "Phoenix")


    Or a "grand total score"


    =(COUNTIFS({Source Sheet Q1 Column}, "Good", {Source Sheet Site Column}, "Phoenix") * 3) + (COUNTIFS({Source Sheet Q1 Column}, "Satisfactory", {Source Sheet Site Column}, "Phoenix") * 2) + COUNTIFS({Source Sheet Q1 Column}, "Does Not Meet Standards", {Source Sheet Site Column}, "Phoenix")

  • Amy Arnold
    Amy Arnold ✭✭✭✭

    We have about twenty sites that we visit throughout the year. While on site, I want a short survey (via SS form) completed. Then I want to share the survey results from that site visit in chart format.


    Below I shared a question, the responses and an example of the chart I might create.


    The form adds the survey results to Smartsheet #1. In this SS, I created the following formula, however, I lose this formula when the survey results are added. This formula returns #UNPARSEABLE so it's not correct.


    =IF([Q1]@row, = "Good Satisfactory", "3"), "", IF([Q1]@row, = "Satisfactory", "2"), IF([Q1]@row, = "Does Not Meet MTC Standards", "1"), IF([Q1]@row, = "Not applicable", "0")


    Because I lose the formula, I created Smartsheet #2. In this sheet, I wanted to convert the text responses to a numeric number (see bullets) so I can create charts. Also, I just want the results from one site, like Phoenix.


    • Good = 3
    • Satisfactory = 2
    • Does Not Meet Standards = 1
    • Not applicable = 0


    The goal is before I leave the site, share the results of the survey and discuss it with them. There's 18 questions on the survey.


    Maybe the path to take is using the summary feature in Smartsheet which I could use to make charts or whatnot. If I used the summary feature, I would need to look at the SITE, MOST RECENT DATE, and then average the responses from the most recent date. Hmmm, that's a lot.


    I appreciate everyone's help.


    p.s. Andrée, I shared the sheets with you.

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

    @Amy Arnold

    Thanks!

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best, 

    Andrée 

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Amy Arnold
    Amy Arnold ✭✭✭✭
    edited 04/10/20

    Hi,

    I appreciate your help. Sorry I didn't respond, I got pulled to another project.


    I created Helper columns for the IF formula Paul shared above. I pasted the formula into the column Q1[Helper] and it returned an #INCORRECT ARGUMENT SET. Everything looks good though. Hmmm, any suggestions for resolving the error?


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

    @Amy Arnold

    Remove the , after each @row part and it should work.

    Did it?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Andrée Starå I was doing a ton of COUNTIFS and COLLECTs that week, and it got to the point where that syntax was just coming from muscle memory. Ugh.

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Stacey Carrasco
    Stacey Carrasco ✭✭✭✭✭

    Hi @Paul Newcome

    I'm using your formula you provided in this thread

    =IF([Q1]@row = "Good", 3, IF([Q1]@row = "Satisfactory", 2, IF([Q1]@row = "Does Not Meet MTC Standards", 1, 0)))

    for a sheet I have but I'm getting an error of #CIRCULAR REFERENCE

    Here's my situation we have a survey that was are distributed with a form. We have some drop down values as:

    0-Strongly Disagree

    1-Disagree

    2-Neutral

    3-Somewhat Agree

    4-Strongly Agree

    N/A

    Of those values we want to have the the number value returned in the sheet cell and not the whole word/text. So should a survey participant answers a question on the form as "0-Strongly Disagree" we want 0 to be in the cell not the text. Can we do that?

    Senior Program Coordinator

    De Anza College

  • Stacey Carrasco
    Stacey Carrasco ✭✭✭✭✭

    @Andrée Starå @Paul Newcome

    Is it possibly to have a formula set up in a column that will return a number in the column instead of the text result from the form without having to create a helper column?

    Senior Program Coordinator

    De Anza College

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!