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
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 444 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 290 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!