Employee Reviews - Averaging scores for different criterea

Options

Been playing around with a few index/match formulas but getting a bit complicated..🤯

To explain:

  • Need to add numeric values to each dropdown to give scores to each critera
  • Then search for each Reviewer that reviewed each employee and add all scores for each critera
  • Finally, average the score =AVG(CHILDREN())

Went ahead and published both sheets if it helps for more reference.

Role Review Testing

Employee Review [METRICS]

Thanks for reading!

Tags:

Answers

  • Mathieu PERSICO
    edited 07/24/20
    Options

    Hi Maricarmen,

    Do you have to follow exactly the Employee Review sheet or can we use a different template ?

    If second option, I think using a template similar to Role Review Testing would be easier in your case.

    Thanks,

    Mathieu | Workflow Consultant

    info@evolytion.com

  • Maricarmen Vargas
    Options

    Ended up creating a helper colum "Employee+Reviewer" in order to be able to index and match both Employee and Reviewer criterias.

    =INDEX($Score$2:$Score$6, MATCH(INDEX({Complete Table}, MATCH($[Primary Column]$8 + " " + [Primary Column]@row, {Help Join}, 0), 3), $[Primary Column]$2:$[Primary Column]$6, 0))

    {Complete Table} is from Employee to Criteria 5

    {Help Join} is Employee+Reviewer


    If anyone has any other idea or optimization, let me know!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!