I have a complex set of circumstances that I want to calculate averages from. The array on the left has data of 3 different candidates that were assessed on 5 different metrics by 4 different assessors. For each candidate, the same assessor that scores for M1 also gives a score for a different metric, such that different combinations of assessors and metrics scored are unique.
From these data, I want to find three fundamentally different types of averages (1-Overall Candidate Avg Score; 2-Composite Avg Exclude Assessor; and 3- Assessor Influence Avg).
1 - this is simple enough and is just the averages of the scores from all 5 metrics for each candidate.
2 - this is a little more complex and calculates four different averages (2A-2D). 2A = the scores for each candidate excluding the assessor that rendered two different scores. For example, YJ gave two scores to candidate 1420 that will be excluded. 2B, 2C, and 2D will calculate averages of 4 scores and cycle through excluding the other assessors' scores, respectively.
3 - this will calculate the impact each assessor has on candidate average scores. To do this, it will find the average absolute difference each assessors' exclusion is from the overall candidate average score -- in other words, how different is any given assessors' opinion from the consensus?
<colgroup><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="208"><col width="207"><col width="206"><col width="204"></colgroup><tbody>
</tbody><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
thank you!!
From these data, I want to find three fundamentally different types of averages (1-Overall Candidate Avg Score; 2-Composite Avg Exclude Assessor; and 3- Assessor Influence Avg).
1 - this is simple enough and is just the averages of the scores from all 5 metrics for each candidate.
2 - this is a little more complex and calculates four different averages (2A-2D). 2A = the scores for each candidate excluding the assessor that rendered two different scores. For example, YJ gave two scores to candidate 1420 that will be excluded. 2B, 2C, and 2D will calculate averages of 4 scores and cycle through excluding the other assessors' scores, respectively.
3 - this will calculate the impact each assessor has on candidate average scores. To do this, it will find the average absolute difference each assessors' exclusion is from the overall candidate average score -- in other words, how different is any given assessors' opinion from the consensus?
| 1 | |||||||||
| candidate | metric | assessor | score | Candidate | Overall Candidate Avg Score | ||||
| 1420 | M1 | YJ | 2 | 1420 | |||||
| 1420 | M2 | MB | 3 | 1452 | |||||
| 1420 | M3 | HG | 3 | 1444 | |||||
| 1420 | M4 | YJ | 2 | 2A | 2B | 2C | 2D | ||
| 1420 | M5 | LT | 4 | Candidate | Composite Avg1 Exclude Assessor2x | Composite Avg2 Exclude Assessor1x | Composite Avg3 Exclude Assessor1x | Composite Avg4 exclude assessor1x | |
| 1452 | M1 | QK | 4 | 1420 | |||||
| 1452 | M2 | QK | 4 | 1452 | |||||
| 1452 | M3 | MD | 1 | 1444 | |||||
| 1452 | M4 | YJ | 1 | 3 | |||||
| 1452 | M5 | MB | 5 | Assessor | Assessor Influence Avg | ||||
| 1444 | M1 | BT | 1 | BT | |||||
| 1444 | M2 | DM | 3 | DM | |||||
| 1444 | M3 | BT | 4 | HG | |||||
| 1444 | M4 | RN | 3 | LT | |||||
| 1444 | M5 | MB | 2 | MB | |||||
| MD | |||||||||
| QK | |||||||||
| RN | |||||||||
| YJ |
<colgroup><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="208"><col width="207"><col width="206"><col width="204"></colgroup><tbody>
</tbody>
thank you!!
