@Kmaso1 you won't be able to do this with just Forms but will need a relatively simple flow in Power Automate to add up the score and apply the priority value and add the result to a table in a different Excel spreadsheet. The result of my example looks like this:
In this example the Form has 5 questions with 5 possible answers, only 1 of which is correct:
You the build the flow in Power Automate as follows:
1. Add a Forms When a new response is submitted trigger followed by the Get response details action. Next add an Office 365 Users Get user profile (v2) action as we'll just add the firstname of the responder into the spreadsheet. But you could omit this step and add the Responder's email address into the spreadsheet instead.
2. You now need to add 2 initialize variable actions for the score (an integer type) and the priority( a string type):
3. Next add a condition for Question 1, select the question from the dynamic content box on the right when you click in the field, select is equal to, then type in the correct answer. In the green If yes channel we want to increase the score by 1 so add an Increment variable action and set the value to 1. Leave the red if no channel empty.
You then need to add a condition the same as for question 1 for all the other questions. I have not done screenshots for these other questions as the only difference is the question and answer in the condition.
4. Next we want to look at what the score is and apply a priority to it. In this example I've assumed that a score of 5 gives a priority of H, 2-4 gives M and less than 2 gives L. So add another condition and in the left hand box select varScore from the dynamic content, then select is equal to, then type in 5. In the green if yes channel add a Set variable action and set the value to H.
5. In the red if no channel add another condition and in the left box select varScore from the dynamic content then select is greater than or equal to and type in 2. Then click the Add button to add another row where you select varScore is less than or equal to 4.
In the green if yes channel add another Set variable action and set the value to M. In the red if no channel add another Set variable action and set the value to L (you can copy actions to your clipboard to make adding actions quicker).
6. Add a new step after all the conditions and add the Excel for Business Add a row into a table action and in the Name column I've just selected the Given name from the dynamic content, then the varScore for the Score and the varPriority for the priority.
I hope the description and screenshots are clear but come back with any questions about this.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
love this and thanks! i had to play a bit as my conditions stopped working based on some long answers (i.e. contains) so I truncated those. I have this working on a form where we are tallying different points per options within a question so many conditions here with increments per answer options
