Using Copilot in Excel to convert survey responses into an overall score
Greetings, We’re continuing our series of posts to show you some of the things that are possible to do with Copilot in Excel. Today I have the results from a recent survey for a training session. I’d like to calculate a satisfaction score for each participant based on the responses for each of the 4 questions.
I use the following prompt to ask Copilot to create a score:
Add a column to calculate the overall satisfaction from the session
Looking at B2:F10, here's 1 formula column to review and insert in Column G:
Overall Satisfaction
Calculates the average satisfaction score for each survey response by converting the first character of each rating to a numeric value, summing these values, and then dividing by 4 (number of questions).
=SUM(VALUE(LEFT([@[Overall, this session was valuable.]],1)), VALUE(LEFT([@[I had opportunities to interact and engage during the session.]],1)), VALUE(LEFT([@[The content was valuable.]],1)), VALUE(LEFT([@[The presentation of the session was effective.]],1)))/4
I hover over the Insert column button to see a preview of the formula that is suggested:
I click on the insert column button, getting the following table which quickly completed my task.
Over the coming weeks I will be sharing more examples of what you can do with Copilot in Excel.
Thanks for reading,
Microsoft Excel Team
*Disclaimer: If you try these types of prompts and they do not work as expected, it is most likely due to our gradual feature rollout process. Please try again in a few weeks.