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 prepopulate 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!