Employee Reviews - Pulling & Averaging Category Scores

Hi all, thanks in advance for your assistance!

I am working on building a new employee review process for my company and am caught up on one of the final steps. Here's where I am at:

I have created an employee review form that individual managers will complete
Each employee will have 3-5 reviewers
The form submissions will go to the "Review Log"
I have created a tracker that has employees listed on lines and the number of reviewers and average scores listed in columns.

This is where I am getting hung up. On the tracker, I am wanting to pull all the reviewer scores from the log associated with that employee and average them per category. I have attempted to use AI to assist with this but I am only getting #UNPARSEABLE back in the cells.

Find more posts tagged with

Sheets

Formulas

functionality

Automations

Accepted answers

Paul Newcome

You would use a COUNTIFs to get the number of reviews and then an AVG/COLLECT to get your averages.

=COUNTIFS({Employee Name}, @cell = [Employee Name]@row)

=AVG(COLLECT({Safety}, {Employee Name}, @cell = [Employee Name]@row))

All comments

Cayla Davis

Hi @mcline - I am trying to understand what you are trying to do based on your screenshots.

Is it correct in saying that you want to take the Average Safety Score, Average Teamwork Score, Average Efficiency Score, etc and take those averages and put them into the Average Score per person?

mcline

Yes, the top screenshot shows an example of 2 employees that have received 3 managerial reviews. The bottom screenshot is another sheet that will show how many reviews an employee received and then the average per category from all their reviewer scores.

Paul Newcome

You would use a COUNTIFs to get the number of reviews and then an AVG/COLLECT to get your averages.

=COUNTIFS({Employee Name}, @cell = [Employee Name]@row)

=AVG(COLLECT({Safety}, {Employee Name}, @cell = [Employee Name]@row))

mcline

Paul - Thank you! What do I need to do to modify this formula for the other categories? I tried changing it to

=AVG(COLLECT({Teamwork}, {Employee Name}, @cell = [Employee Name]@row )) and

=AVG(COLLECT({Efficiency}, {Employee Name}, @cell = [Employee Name]@row )) but it is giving me #INVALID REF

mcline

I got it to work for Safety and Teamwork, but when I try to do the other categories and change the formula and reference the log, it gives me a #BLOCKED error now

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.