Weighted Average  Employee Scorecard
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 dropdown but is not restricted to dropdown 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 subgoal 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.
Best Answer

We should be able to use this:
=IFERROR(([Employee Rating]39 * 0.2) + ([Employee Rating]35 * 0.8), "")
Answers

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

The number changes to 1.55

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?

Yes, exactly. Ratings are 15, 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.

We should be able to use this:
=IFERROR(([Employee Rating]39 * 0.2) + ([Employee Rating]35 * 0.8), "")

That worked! You're a genius, thank you!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!