I have a evaluation form that I NEED help with. There are 4 Rating levels to choose, E, S, NI, and NA with a value of 3, 2, 1, and 0. Each Category (Blue row) has subtask (Green rows) with a percentage weight for each. In the example below, you can see in Column F that each weight is 25% for all 4 subtask. When I make the last task an NA (cell C13), then the other 3 subtask weight changes to 33.33%, If I change 2 to NA then the remaining changes to 50%. Easy as there are only 4 subtask and each are 25
But on the following Category, there are 7 subtask with different weight (20%, 15%, and 10%). So on this one, if I make a subtask NA, then that weight needs to be distributed to the other 6 subtasks but keep the same ration.
So making subtask 1 an NA, then the other 6 task would need to be their original % (Column E) PLUS and additional 4%(20%/6) So 10% changes to 13.3 and 15% changes to 18.3. If I then also make Subtask 6 an NA, then the remaining 5 will get and additional 7% (35%/5 [20 and 15]). So whichever subtask is made an NA, the remaining task need to change appropriately.
| Book1 | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | Rating Category | Actual Rating | Cumm. Rating | Score | Weight | Weight (0.0 to 1.0) | Points | Weighted Score | Preserved Score | ||||||||
| 2 | II. Communication / Facilitating Change | S | 2.00 | 20.0% | 20.0% | 2 | 0.40 | 0.400 | |||||||||
| 3 | Action | N/A Count | 0 | 1 | 2 | 3 | |||||||||||
| 4 | 1. Recommends Innovative Ideas | E | 3 | 33.3% | 25.0% | 1.00 | 1 | 0.25 | 0.3333 | 0.5 | 1 | ||||||
| 5 | Action | ||||||||||||||||
| 6 | Evaluation Justification: | ||||||||||||||||
| 7 | 2. Seeks Personal Training Development Opportunities | S | 2 | 33.3% | 25.0% | 0.67 | |||||||||||
| 8 | Action | ||||||||||||||||
| 9 | Evaluation Justification: | ||||||||||||||||
| 10 | 3. Written Communication | NI | 1 | 33.3% | 25.0% | 0.33 | |||||||||||
| 11 | Action | ||||||||||||||||
| 12 | Evaluation Justification: | ||||||||||||||||
| 13 | 4. Verbal communication and information sharing | NA | 0 | 0.0% | 25.0% | 0.00 | |||||||||||
| 14 | Action | ||||||||||||||||
| 15 | Evaluation Justification: | 100.0% | 100.0% | ||||||||||||||
IT Bus Analyst | |||||||||||||||||
But on the following Category, there are 7 subtask with different weight (20%, 15%, and 10%). So on this one, if I make a subtask NA, then that weight needs to be distributed to the other 6 subtasks but keep the same ration.
| Book1 | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 16 | III. Job Knowledge & Performance | NI | 1.50 | 25.0% | 25.0% | 1 | 0.25 | 0.375 | ||||||||||||
| 17 | To demonstrate knowledge, understanding and proper application of information in the performance of job duties. Ability to efficiently manage time and resources. | 1 | 0 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||||||
| 18 | SubTask 1 | NA | 0 | 0.0% | 20.0% | 0.00 | 0 | 0.16667 | 0.2 | 0.25 | 0.33333 | 0.5 | 1 | |||||||
| 19 | Action | |||||||||||||||||||
| 20 | Evaluation Justification: | |||||||||||||||||||
| 21 | SubTask 2 | S | 2 | 15.0% | 15.0% | 0.30 | ||||||||||||||
| 22 | Evaluation Justification: | |||||||||||||||||||
| 23 | SubTask 3 | NI | 1 | 15.0% | 15.0% | 0.15 | ||||||||||||||
| 24 | Action | |||||||||||||||||||
| 25 | Evaluation Justification: | |||||||||||||||||||
| 26 | SubTask 4 | E | 3 | 10.0% | 10.0% | 0.30 | ||||||||||||||
| 27 | Action | |||||||||||||||||||
| 28 | Evaluation Justification: | |||||||||||||||||||
| 29 | SubTask 5 | E | 3 | 10.0% | 10.0% | 0.30 | ||||||||||||||
| 30 | Action | |||||||||||||||||||
| 31 | Evaluation Justification: | |||||||||||||||||||
| 32 | SubTask 6 | NA | 0 | 0.0% | 15.0% | 0.00 | ||||||||||||||
| 33 | Action | |||||||||||||||||||
| 34 | Evaluation Justification: | |||||||||||||||||||
| 35 | SubTask 7 | E | 3 | 15.0% | 15.0% | 0.45 | ||||||||||||||
| 36 | Action | |||||||||||||||||||
| 37 | Evaluation Justification: | 65.0% | 100.0% | |||||||||||||||||
IT Bus Analyst | ||||||||||||||||||||
So making subtask 1 an NA, then the other 6 task would need to be their original % (Column E) PLUS and additional 4%(20%/6) So 10% changes to 13.3 and 15% changes to 18.3. If I then also make Subtask 6 an NA, then the remaining 5 will get and additional 7% (35%/5 [20 and 15]). So whichever subtask is made an NA, the remaining task need to change appropriately.
