Tricky math problem for employee evaluation engine

[twister212]

TASK
Create the engine to support an employee performance evaluation system.

DESCRIPTION
-Twelve equally weighted characteristics being evaluated (e.g. ability to work under pressure; Teamwork skills, etc).

-Each of the twelve characteristics are “graded”. There are five grades, “Significantly Exceeds Expectations”, “Exceeds Expectations”, “Meets Expectations”, “Needs Improvement”, “Does not meet Expectations”.

-Each grade has a numeric score attached to it -- these scores need to be variable so that the scale can be adjusted slightly if needed. The scores, initially are:

Significantly Exceeds Expectation – 100
Exceeds Expectations – 90
Meets Expectations – 80
Needs Improvement – 70
Does not meet Expectations – 0

-The evaluation needs to drive an average score. So, for example, if eleven characteristics are scored 100 and one is 0, then the average is 91.67. If five characteristics are scored 100 and seven are 80, then the weighted average is 88.3. Basic math.

Here is where it gets tricky:

1. My client would like to see a table (like the one below with only four "grades") that shows every combination of scores and the average score for each combination. This table should allow the adjusting of the numerical grade associated with each qualitative grade to ensure that the mathematical averages make intuitive sense. As a made-up example, it may be that someone who has a “does not meet expectations” score, like in our example above, could not possibly receive an average of 91.76, a great grade, in which case the baseline scores would need to be tweaked to lower the averages that result from “does not meet expectations”.

TABLE
Number of ratings within each category (out of 12)
A B C F Average Overall Grade
100 90 80 -
12 0 0 0 100.00 100%
11 1 0 0 99.17 100%
11 0 1 0 98.33 100%
10 2 0 0 98.33 100%
10 1 1 0 97.50 100%
9 3 0 0 97.50 100%
10 0 2 0 96.67 100%
9 2 1 0 96.67 100%
8 4 0 0 96.67 100%
9 1 2 0 95.83 100%
8 3 1 0 95.83 100%
7 5 0 0 95.83 100%
9 0 3 0 95.00 100%
8 2 2 0 95.00 100%
7 4 1 0 95.00 100%
6 6 0 0 95.00 100%
8 1 3 0 94.17 90%
7 3 2 0 94.17 90%
6 5 1 0 94.17 90%
5 7 0 0 94.17 90%
8 0 4 0 93.33 90%
7 2 3 0 93.33 90%


2. The chart should be built such that the total number of questions can be increased or decreased from the initial twelve questions.

Here is my the issue:
How do I build this table to include each and every combination of scores when there are twelve questions and five “grades” -- using formulas rather than manually, or at least some formulas. I may need to add questions, so it becomes thirteen or fourteen questions too.

Any ideas? Is this a Fibonacci problem?

Thank you!!

Mark

 

[shg]

I am not sure I understand why the client wants to see only four "grades" when there are actually five (1-“Significantly Exceeds Expectations”, 2-“Exceeds Expectations”, 3-“Meets Expectations”, 4-“Needs Improvement”, 5-“Does not meet Expectations”)

If all categories are rated, the fifth is redundant, n'est ce pa?

 

[shg]

Aslo, I believe there are 1820 possible combinations,

= COMBIN(numCategories + numQuestions - 1, numQuestions)

... starting with

0,0,0,0,12 (fly-blown carcass)

... and ending with

12,0,0,0,0 (walks on water)