Forum Discussion
HELP - need help scoring a Microsoft form with worded answers
Hello.
I have created a form with multiple questions asking the user to select a single answer between strongly disagree up to strongly agree (5 possible answers).
I want to be able to turn each answer into a numeric value (1-5) and then be able to total those answers to give a numeric total. I then need this numeric total to be converted back into a word depending on its value. For example 1-5 = very cautious, 6-10 = cautious etc.
I am not sure whether this can be done through forms or whether I need to also use power automate.
Any help would be great.
olliemonnery A solution to this is shown in the screenshots below. It's not a difficult flow, just rather wide due to the parallel branches and Switch/Case actions.
In jy example I've just used a likert scale question as each statement gets treated as a separate question & answer, but it would work equally well with separate questions.
Next, I've used a SharePoint list and there needs to be a number column for each of the questions, a calculalted column for the total and a single line of text column for the "overall" word:
In your screenshot I noticed that you'd used a Forms template. Don't use it, there is an error in the temlate that puts in that unnecessary & incorrect apply to each and causes lots of problems. Unfortunately Microsoft never seem to update or correct their templates. So start your flow from a new, blank instant cloud flow.
Next add a series of parallel branches, one for each question. The first action in each branch should be initialize variable, give it a name, set it to integer and leave the value empty. It will hold the score a bit later.
Next, underneath each variable add a Switch control which is a type of condition that only looks at one value which it must equal. It will add an initial case (in case the question is answered Agree then.....). Add as many cases as you need.
As there are 5 possible answers we need 4 cases plus the default (in case the answer doesn't match any of the other cases). Enter the answer in in the Equals field.
Inside each case add a set variable action, select your variable and set the value:
Next, click new stp so you are adding the next action outside the parallel branches add a SharePoint create item action, select your site and list and in each field select the relevant variable:
Next, add a Compose action and add the folllowing expression (from the expression tab in the dyhnamic content box):
int(outputs('Create_item')?['body/Total'])Next, add an initialize variable to store the "overall" word, set it to string and leave the value empty:
Next, add a Condition. In this example it is if the outputs of the compose with the total in it is greater than 20. In the green if yes channel add a set variable and set your variable to Very Brave.
In the red if no channel add another condition where the outpits of the total is greater than or equal to 15 and less than or equal to 20. Again set the overall word. Keep adding more conditions amd set variable for the word until you have taken account of all possible range of scores.
Finally, add a SharePoint update item action and set the column with the overall word to be the variable that's holding that word.
The result looks like this:
Come back with any questions about all this.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
olliemonnery it needs a flow in Power Automate to do this. You would use a Switch action for each question and depending on the answer would add a number to a variable. A condition would then take that variable and depending on the number would add the relevant text to an email back to the responder.
Rob
Los Gallardos
Microsoft Power Automate Community Super UserThanks Rob. Do you have an example of this?
Also, I am trying to get the total score added to my excel spreadsheet as an extra column to go along with the responses rather than an email to the responder.
I am struggling to find a similar flow online.
olliemonnery A solution to this is shown in the screenshots below. It's not a difficult flow, just rather wide due to the parallel branches and Switch/Case actions.
In jy example I've just used a likert scale question as each statement gets treated as a separate question & answer, but it would work equally well with separate questions.
Next, I've used a SharePoint list and there needs to be a number column for each of the questions, a calculalted column for the total and a single line of text column for the "overall" word:
In your screenshot I noticed that you'd used a Forms template. Don't use it, there is an error in the temlate that puts in that unnecessary & incorrect apply to each and causes lots of problems. Unfortunately Microsoft never seem to update or correct their templates. So start your flow from a new, blank instant cloud flow.
Next add a series of parallel branches, one for each question. The first action in each branch should be initialize variable, give it a name, set it to integer and leave the value empty. It will hold the score a bit later.
Next, underneath each variable add a Switch control which is a type of condition that only looks at one value which it must equal. It will add an initial case (in case the question is answered Agree then.....). Add as many cases as you need.
As there are 5 possible answers we need 4 cases plus the default (in case the answer doesn't match any of the other cases). Enter the answer in in the Equals field.
Inside each case add a set variable action, select your variable and set the value:
Next, click new stp so you are adding the next action outside the parallel branches add a SharePoint create item action, select your site and list and in each field select the relevant variable:
Next, add a Compose action and add the folllowing expression (from the expression tab in the dyhnamic content box):
int(outputs('Create_item')?['body/Total'])Next, add an initialize variable to store the "overall" word, set it to string and leave the value empty:
Next, add a Condition. In this example it is if the outputs of the compose with the total in it is greater than 20. In the green if yes channel add a set variable and set your variable to Very Brave.
In the red if no channel add another condition where the outpits of the total is greater than or equal to 15 and less than or equal to 20. Again set the overall word. Keep adding more conditions amd set variable for the word until you have taken account of all possible range of scores.
Finally, add a SharePoint update item action and set the column with the overall word to be the variable that's holding that word.
The result looks like this:
Come back with any questions about all this.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
