Multi-variable averages of select data

[AdMission]

I posted a question a couple of weeks ago and received some great help. Unfortunately, I was not as precise as I needed to be in how I described what I needed to accomplish. Thus, I will try see if I can get some help for this last bit of formula-building....

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.

[TABLE="width: 1105"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Candidates[/TD]
[TD="class: xl66, width: 87"]Composite Score[/TD]
[TD="class: xl66, width: 87"][/TD]
[TD="class: xl66, width: 87"]Candidate[/TD]
[TD="class: xl66, width: 87"]Assessor[/TD]
[TD="class: xl66, width: 87"]Exclude Score1[/TD]
[TD="class: xl66, width: 87"]Assessor[/TD]
[TD="class: xl66, width: 87"]Exclude Score2[/TD]
[TD="class: xl66, width: 87"]Assessor[/TD]
[TD="class: xl66, width: 87"]Exclude Score3[/TD]
[TD="class: xl66, width: 87"]Assessor[/TD]
[TD="class: xl66, width: 87"]Exclude Score4[/TD]
[TD="class: xl66, width: 87"]Assessor[/TD]
[TD="class: xl66, width: 87"]Exclude Score5[/TD]
[TD="class: xl66, width: 87"]Assessor[/TD]
[TD="class: xl66, width: 87"]Exclude Score6[/TD]
[TD="class: xl67, width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl68"]AA[/TD]
[TD="class: xl69"]2.857[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]AA[/TD]
[TD="class: xl70"]YJ[/TD]
[TD="class: xl70"]3.2[/TD]
[TD="class: xl70"]MB[/TD]
[TD="class: xl69"]2.833[/TD]
[TD="class: xl70"]NA[/TD]
[TD="class: xl69"]2.833[/TD]
[TD="class: xl70"]LT[/TD]
[TD="class: xl69"]2.667[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"]AB[/TD]
[TD="class: xl69"]3.286[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]AB[/TD]
[TD="class: xl70"]QK[/TD]
[TD="class: xl70"]3[/TD]
[TD="class: xl70"]LT[/TD]
[TD="class: xl69"]3.667[/TD]
[TD="class: xl70"]GS[/TD]
[TD="class: xl69"]3.667[/TD]
[TD="class: xl70"]FR[/TD]
[TD="class: xl69"]3.000[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"]AC[/TD]
[TD="class: xl69"]2.429[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]AC[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]VL[/TD]
[TD="class: xl69"]2.667[/TD]
[TD="class: xl70"]MB[/TD]
[TD="class: xl69"]2.333[/TD]
[TD="class: xl70"]TB[/TD]
[TD="class: xl69"]2.167[/TD]
[TD="class: xl70"]NA[/TD]
[TD="class: xl69"]2.333[/TD]
[TD="class: xl70"]BS[/TD]
[TD="class: xl69"]2.667[/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"]AD[/TD]
[TD="class: xl69"]1.714[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]AD[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]BK[/TD]
[TD="class: xl69"]1.500[/TD]
[TD="class: xl70"]HK[/TD]
[TD="class: xl69"]1.833[/TD]
[TD="class: xl70"]LL[/TD]
[TD="class: xl69"]1.833[/TD]
[TD="class: xl70"]MG[/TD]
[TD="class: xl69"]1.833[/TD]
[TD="class: xl70"]MA[/TD]
[TD="class: xl69"]1.667[/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"]AE[/TD]
[TD="class: xl69"]1.714[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]AE[/TD]
[TD="class: xl70"]MA[/TD]
[TD="class: xl70"]1.6[/TD]
[TD="class: xl70"]RS[/TD]
[TD="class: xl69"]1.833[/TD]
[TD="class: xl70"]DM[/TD]
[TD="class: xl69"]1.667[/TD]
[TD="class: xl70"]HK[/TD]
[TD="class: xl69"]1.500[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"]AF[/TD]
[TD="class: xl69"]1.286[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]AF[/TD]
[TD="class: xl70"]VL[/TD]
[TD="class: xl70"]1.2[/TD]
[TD="class: xl70"]TB[/TD]
[TD="class: xl69"]1.167[/TD]
[TD="class: xl70"]MA[/TD]
[TD="class: xl69"]1.333[/TD]
[TD="class: xl70"]MG[/TD]
[TD="class: xl69"]1.333[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"]AG[/TD]
[TD="class: xl69"]2.714[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]AG[/TD]
[TD="class: xl70"]BL[/TD]
[TD="class: xl70"]2.4[/TD]
[TD="class: xl70"]JJ[/TD]
[TD="class: xl69"]2.667[/TD]
[TD="class: xl70"]BK[/TD]
[TD="class: xl69"]2.500[/TD]
[TD="class: xl70"]LT[/TD]
[TD="class: xl69"]2.833[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"]AI[/TD]
[TD="class: xl69"]2.429[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]AI[/TD]
[TD="class: xl70"]FA[/TD]
[TD="class: xl70"]2.4[/TD]
[TD="class: xl70"]PK[/TD]
[TD="class: xl69"]2.167[/TD]
[TD="class: xl70"]LE[/TD]
[TD="class: xl69"]2.333[/TD]
[TD="class: xl70"]MB[/TD]
[TD="class: xl69"]2.667[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]Exclude Difference Avg[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]BK[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]BL[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]BS[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]DM[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]FA[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]FR[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]GS[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]HK[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]JJ[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]LE[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]LL[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]LT[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]MA[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]MB[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]MG[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]NA[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]PK[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]QK[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]RS[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]TB[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]VL[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl73"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"]YJ[/TD]
[TD="class: xl75"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl76"][/TD]
[/TR]
</tbody>[/TABLE]

 

[mopp1]

Assuming Candidates is in A1

CSE formula (enter with Ctrl-Shift-Enter) in D14 and drag down

=AVERAGE(IF(MMULT(IFERROR(--($E$2:$P$9=C14)*$F$2:$Q$9,0),ROW($1:$12)^0)>0,ABS(MMULT(IFERROR(--($E$2:$P$9=C14)*$F$2:$Q$9,0),ROW($1:$12)^0)-$B$2:$B$9),""))

Note: If you add any rows above row 1 it will change the "ROW($1:$12)" and the formula will need to be readjusted

 

[AdMission]

Works like an effing charm!! Thanks to you and all others who provide such amazing brainpower.

 

[AdMission]

Assuming Candidates is in A1

CSE formula (enter with Ctrl-Shift-Enter) in D14 and drag down

=AVERAGE(IF(MMULT(IFERROR(--($E$2:$P$9=C14)*$F$2:$Q$9,0),ROW($1:$12)^0)>0,ABS(MMULT(IFERROR(--($E$2:$P$9=C14)*$F$2:$Q$9,0),ROW($1:$12)^0)-$B$2:$B$9),""))

Note: If you add any rows above row 1 it will change the "ROW($1:$12)" and the formula will need to be readjusted

The formula works well, but when I try to institute it into my actual data set, which is much larger, it won't tolerate it, and I get the dreaded #VALUE !, despite making what appears to be all the commensurate adjustments to the formula.

That said, I am unclear as to why the formula does certain things. For example, why is it ROWs 1-12, when the data is in 2-9? I think if I knew the rationale behind that, I might be able to rectify.

As it stands, my real data set is structured the same way as I have said above with the following differences:

- instead of 6 exclude scores there are 8, so the data does not stop at column P, it goes to T
- instead of 8 candidates there are 578, so the data does not stop at row 9, it goes to row 579
- instead of 22 assessors that are listed from C14:C35, there are 126 from C584:C709

With those differences, the formula was modified in the following way, which does not seem to work when put in D584:

CSE =AVERAGE(IF(MMULT(IFERROR(--($E$2:$T$579=C584)*$F$2:$U$579,0),ROW($1:$582)^0)>0,ABS(MMULT(IFERROR(--($E$2:$T$579=C584)*$F$2:$U$579,0),ROW($1:$582)^0)-$B$2:$B$579),""))

 

[mopp1]

The ROW($1:$12)^0 just returns and array of twelve 1s, the number equivalent to the number of columns of interest (6 reviewers x 2 columns per reviewer = 12). Since you are using 8 reviewers you would change it to ROW($1:$16)^0 to return an array of sixteen 1s.

Change your revised formula to replace ROW($1:$582) to ROW($1:$16), CSE and see if it works.

If you want it to be more intuitive you can change the ROW($1:$16)^0 to TRANSPOSE(COLUMN($E$2:$T$579))^0 (both return an identical array of sixteen 1s).

 

[AdMission]

Light bulb!!