Formula to average scores from form responses
Hello,
I am building a repository for Interview Evaluations. I have created a form to collect answers from the interviewers to rate candidates.
Interviewers are asked to score candidate competencies in multiple areas. I have assigned point value to each answer (2 points for Strongly Agree, 1 point for Agree, 0 for Neutral, -1 Disagree, -2 Strongly Disagree). These point values are totaled up in the "Total points out of possible 34" column, using helper columns.
I will have multiple interviewer forms (multiple rows) for each candidate. I would like to summarize the point averages for each candidate.
What I need is a formula to:
- Match all lines with the same candidate name
- Pull all of the total scores (from each line)
- Calculate an average of scores (total scores added up divided by # of interviewer responses)
Any help would be greatly appreciated.
Thank you!
Best Answer
-
Hello @mwalton86 I'm a little confused from your screenshot if you are trying to sum all candidate responses into an overall average, do an average per candidate, or both.
Average for all candidates:
=AVG([Total Points out of possible 34]:[Total Points out of possible 34])
Which returns the expected 12.5
Average for each individual candidate:
You could either create a summary field for each candidate or do the averaging on a column formula (depends what you're doing with it in the end).
Summary Field example:
=AVERAGEIF([Candidate's Name]:[Candidate's Name], "John Smith", [Total Points out of possible 34]:[Total Points out of possible 34])
Column formula example:
=AVERAGEIF([Candidate's Name]:[Candidate's Name], [Candidate's Name]@row, [Total Points out of possible 34]:[Total Points out of possible 34])
Answers
-
Hello @mwalton86 I'm a little confused from your screenshot if you are trying to sum all candidate responses into an overall average, do an average per candidate, or both.
Average for all candidates:
=AVG([Total Points out of possible 34]:[Total Points out of possible 34])
Which returns the expected 12.5
Average for each individual candidate:
You could either create a summary field for each candidate or do the averaging on a column formula (depends what you're doing with it in the end).
Summary Field example:
=AVERAGEIF([Candidate's Name]:[Candidate's Name], "John Smith", [Total Points out of possible 34]:[Total Points out of possible 34])
Column formula example:
=AVERAGEIF([Candidate's Name]:[Candidate's Name], [Candidate's Name]@row, [Total Points out of possible 34]:[Total Points out of possible 34])
-
Thanks @ericncarr
The column formula worked well! I was confused how to average for each candidate separately (without having to pre-populate their names in the formula).
Thanks again!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 217 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!