Weighted Average - Employee Scorecard

Sam C

I can't quite get this formula right. I'm attempting to create a scorecard where competencies account for 50% (all competency categories are equal) and goals are 50% (performance is 80% and development is 20%). The column is a drop-down but is not restricted to drop-down options.

The formula I'm using is =AVGW([Employee Rating]39 * 0.2, [Employee Rating]35 * 0.8) but the figure is incorrect; it should be between 3 and 3.5, but it's showing 0.70. The two fields that it is using are also calculated fields, =AVG([Employee Rating]36:[Employee Rating]38 where each sub-goal row is equal. Is that my issue? What am I doing wrong?

I think once we can solve for this, I can add the other column for manager ratings and then ultimately the total rating of competencies and goals.

Paul Newcome

We should be able to use this:

=IFERROR(([Employee Rating]39 * 0.2) + ([Employee Rating]35 * 0.8), "")

Paul Newcome

What happens if you just use the AVG function instead of the AVGW function since you are inputting your own weights?

Sam C

The number changes to 1.55

Paul Newcome

And that makes sense now that I look at it. Sorry.

What is the top rating for each of the child rows? Is it a 1 - 5 scoring type of thing?

Sam C

Yes, exactly. Ratings are 1-5, and when a rating hasn't been selected, the field option is "Select your rating", but that returns a "#Divide by Zero" error, which is acceptable while we're waiting on ratings.

Paul Newcome

We should be able to use this:

=IFERROR(([Employee Rating]39 * 0.2) + ([Employee Rating]35 * 0.8), "")

Sam C

That worked! You're a genius, thank you!