I have a number candidates that have received a composite score... this is a figure comprised of 5 individual scores; however, in some cases the composite score is from five different individuals, while in other cases the 5 scores come from 4 individuals, one of which has given two different scores.

One of the things I am trying to do is determine how good the assessors are at assessing. One of the ways I am doing this is through an exclusion analysis. In this analysis, individual assessor scores are excluded from the calculation of the candidate's composite score. In this way, it is possible to see the influence of any one assessor on the composite score that a candidate attains. The larger the difference the exclusion score is from the composite score, the greater the influence an assessor had on the outcome.

With help from this board, I have managed to build a formula that derives the Candidate Composite scores, as well as generate output for the exclusion analysis. What I would like to do now is to calculate an exclusion difference average for each assessor.

Below, is a snippet of the data, where 8 candidates have a composite score, and on the right are the exclusion analyses for the assessors. What I would like to do is build a formula that calculates the difference of the exclude score from the composite score and determine the average exclusion difference for each assessor in the cells below.

I'd be happy to answer any questions...thank you.

Candidates | Composite Score | Candidate | Assessor | Exclude Score1 | Assessor | Exclude Score2 | Assessor | Exclude Score3 | Assessor | Exclude Score4 | Assessor | Exclude Score5 | Assessor | Exclude Score6 | ||

AA | 2.857 | AA | YJ | 3.2 | MB | 2.833 | NA | 2.833 | LT | 2.667 | ||||||

AB | 3.286 | AB | QK | 3 | LT | 3.667 | GS | 3.667 | FR | 3.000 | ||||||

AC | 2.429 | AC | VL | 2.667 | MB | 2.333 | TB | 2.167 | NA | 2.333 | BS | 2.667 | ||||

AD | 1.714 | AD | BK | 1.500 | HK | 1.833 | LL | 1.833 | MG | 1.833 | MA | 1.667 | ||||

AE | 1.714 | AE | MA | 1.6 | RS | 1.833 | DM | 1.667 | HK | 1.500 | ||||||

AF | 1.286 | AF | VL | 1.2 | TB | 1.167 | MA | 1.333 | MG | 1.333 | ||||||

AG | 2.714 | AG | BL | 2.4 | JJ | 2.667 | BK | 2.500 | LT | 2.833 | ||||||

AI | 2.429 | AI | FA | 2.4 | PK | 2.167 | LE | 2.333 | MB | 2.667 | ||||||

Exclude Difference Avg | ||||||||||||||||

BK | ||||||||||||||||

BL | ||||||||||||||||

BS | ||||||||||||||||

DM | ||||||||||||||||

FA | ||||||||||||||||

FR | ||||||||||||||||

GS | ||||||||||||||||

HK | ||||||||||||||||

JJ | ||||||||||||||||

LE | ||||||||||||||||

LL | ||||||||||||||||

LT | ||||||||||||||||

MA | ||||||||||||||||

MB | ||||||||||||||||

MG | ||||||||||||||||

NA | ||||||||||||||||

PK | ||||||||||||||||

QK | ||||||||||||||||

RS | ||||||||||||||||

TB | ||||||||||||||||

VL | ||||||||||||||||

YJ |

<tbody>

</tbody>