Weighted values in Excel

Question

Question

I am developing a progress report for individuals. Each day, the individual is evaluated with about 20 different criteria with a rating of meets expectations, fails expectations or exceeds expectations. However, some of the criteria is more important than others and needs to be weighted heavier. And, some of the criteria may not be evaluated on that day and shouldn't be counted against the individual. Any ideas on how to do this and have it weigh the values fairly?

I have included my formulas that I used. Does this look correct? The weighted average result is concerning me. The more I evaluate, the weighted score goes above 100. Should that be the case?

Thanks

Scoring formula I used:

How my weighted formula is set up:

Answer 1

Book2.xlsx

Answer 2

Let me know if this works.

Answer 3

Given the structure of your table and the way that data is entered, I think that it would be better to give a points score rather than a percent.