Employee Reviews - Averaging scores for different criterea

Maricarmen Vargas

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!

Mathieu PERSICO

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,

Maricarmen Vargas

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!