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

Amy Arnold

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

Paul Newcome

You can use this formula in the source sheet. Just make sure you remove that field from the form completely. Then autofill should pull it to new entries.

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

Then in your Target sheet, you would create an additional date type column and use a formula such as below in the same row of the site...

=MAX(COLLECT({Source Sheet Entry Date Column}, {Source Sheet Site Column}, "Phoenix")

Then to get your average score for the most recent visit to Phoenix...

=AVG(COLLECT({Source Sheet Number Column}, {Source Sheet Entry Date Column}, @cell = [Target Sheet Date Column]@row, {Source Sheet Site Column}, "Phoenix"))

Paul Newcome

I am so sorry. I was going through A LOT of COUNTIFS formulas and ended up with the wrong syntax. Too many commas.

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

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!

Amy Arnold

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å

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, andree@getdone.se)

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

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.

Paul Newcome

You can use this formula in the source sheet. Just make sure you remove that field from the form completely. Then autofill should pull it to new entries.

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

Then in your Target sheet, you would create an additional date type column and use a formula such as below in the same row of the site...

=MAX(COLLECT({Source Sheet Entry Date Column}, {Source Sheet Site Column}, "Phoenix")

Then to get your average score for the most recent visit to Phoenix...

=AVG(COLLECT({Source Sheet Number Column}, {Source Sheet Entry Date Column}, @cell = [Target Sheet Date Column]@row, {Source Sheet Site Column}, "Phoenix"))

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 

Amy Arnold

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?

Paul Newcome

I am so sorry. I was going through A LOT of COUNTIFS formulas and ended up with the wrong syntax. Too many commas.

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

Andrée Starå

@Amy Arnold

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

Did it?

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å

@Paul Newcome

https://media.giphy.com/media/ctkBfE47GZEtbYUk41/giphy.gif

Paul Newcome

🤣🤣

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?

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?