Formula to average scores from form responses

mwalton86

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:

1. Match all lines with the same candidate name
2. Pull all of the total scores (from each line)
3. Calculate an average of scores (total scores added up divided by # of interviewer responses)

Faculty candidate evaluations.png

Any help would be greatly appreciated.

Thank you!

ericncarr

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])

ericncarr

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])

mwalton86

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!