Conditional averages from arrayed data with mutiple variables

[AdMission]

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?


[TABLE="width: 0"]
<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>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]candidate[/TD]
[TD="align: center"]metric[/TD]
[TD="align: center"]assessor[/TD]
[TD="align: center"]score[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Candidate
[/TD]
[TD="align: center"]Overall Candidate Avg Score
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1420[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]YJ[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1420[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1420[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1452[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1420[/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]HG[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1444[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1420[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"]YJ[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2A
[/TD]
[TD="align: center"]2B
[/TD]
[TD="align: center"]2C
[/TD]
[TD="align: center"]2D
[/TD]
[/TR]
[TR]
[TD="align: center"]1420
[/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Candidate
[/TD]
[TD="align: center"]Composite Avg1 Exclude Assessor2x
[/TD]
[TD="align: center"]Composite Avg2 Exclude Assessor1x
[/TD]
[TD="align: center"]Composite Avg3 Exclude Assessor1x
[/TD]
[TD="align: center"]Composite Avg4 exclude assessor1x
[/TD]
[/TR]
[TR]
[TD="align: center"]1452[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]QK[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1420[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1452[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]QK[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1452[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1452[/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]MD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1444[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1452[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"]YJ[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1452[/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Assessor
[/TD]
[TD="align: center"]Assessor Influence Avg
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1444[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]BT[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]BT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1444[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]DM[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]DM[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1444[/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]BT[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]HG[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1444[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"]RN[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1444[/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]MD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]QK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]RN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]YJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
thank you!!

 

[Eric W]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]candidate[/TD]
[TD="align: center"]metric[/TD]
[TD="align: center"]assessor[/TD]
[TD="align: center"]score[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Candidate[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Overall Candidate Avg Score[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1420[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]YJ[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1420[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1420[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1452[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1420[/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]HG[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1444[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2.6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1420[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"]YJ[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2B[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2C[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2D[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1420[/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Candidate[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Composite Avg1 Exclude Assessor2x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Composite Avg2 Exclude Assessor1x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Composite Avg3 Exclude Assessor1x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Composite Avg4 exclude assessor1x[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1452[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]QK[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1420[/TD]
[TD="align: center"]YJ[/TD]
[TD="align: center"]3.333333333[/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"]2.75[/TD]
[TD="align: center"]HG[/TD]
[TD="align: center"]2.75[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]2.5[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1452[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]QK[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1452[/TD]
[TD="align: center"]QK[/TD]
[TD="align: center"]2.333333333[/TD]
[TD="align: center"]MD[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]YJ[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"]2.5[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1452[/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]MD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1444[/TD]
[TD="align: center"]BT[/TD]
[TD="align: center"]2.666666667[/TD]
[TD="align: center"]DM[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]RN[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"]2.75[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]1452[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"]YJ[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]1452[/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Assessor[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Assessor Influence Avg[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]1444[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]BT[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]BT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.066666667[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]1444[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]DM[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]DM[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]1444[/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]BT[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]HG[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.05[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]1444[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"]RN[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]1444[/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]MB[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]MD[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]QK[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.666666667[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]RN[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]YJ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1.033333333[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H3[/TH]
[TD="align: left"]=AVERAGEIFS(D:D,A:A,F3)[/TD]
[/TR]
[TR]
[TH]H8[/TH]
[TD="align: left"]=AVERAGEIFS($D:$D,$A:$A,$F8,$C:$C,"<>"&G8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G8[/TH]
[TD="align: left"]{=INDEX($C$3:$C$17,SMALL(IF($A$3:$A$17=$F8,IF(MATCH($A$3:$A$17&"|"&$C$3:$C$17,$A$3:$A$17&"|"&$C$3:$C$17,0)=ROW($C$3:$C$17)-ROW($C$3)+1,ROW($C$3:$C$17)-ROW($C$3)+1)),COLUMNS($F8:G8)/2))}[/TD]
[/TR]
[TR]
[TH]H13[/TH]
[TD="align: left"]{=SUM(IF($G$8:$M$10=F13,ABS($H$8:$N$10-SUMIF($F$3:$F$5,$F$8:$F$10,$H$3:$H$5))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



As you said, the formula for the overall score is quite easy (H3). In order to get the composite average excluding each of the assessors in turn, I found it necessary to add a column which listed each of them. Enter the array formula in G8, then copy it to G9:10, I8:I10, K8:K10, M8:M10. Once that is in place, the formula for the average excluding that assessor is fairly easy (H8). Copy that to all the appropriate cells.

Finally, with those in place, the Assessor Influence average is not too hard (H13). The biggest issue I see is what to do when an assessor shows up more than once. For example, MB is on all 3 candidates, with differences of .05, .5, and .15 which sum to .7. I could divide by the number of times MB appears if you want. You'd just need to change the SUM to AVERAGE in the H13 formula.

In any event, let me know what you think.

 

[AdMission]

Eric, this helps tremendously! I will try it out on my real data set tomorrow (which is very large) and see if it does what I hope. Thanks a million.

 

[markmzz]

Hi AdMission!

Try this small modification in Eric's suggestion:

In H2 and copy down

=AVERAGEIF(A$2:A$16,F2,D$2:D$16)

In G7 the Array Formula (use Ctrl+Shift+Enter to enter the formula) and copy down

=INDEX(C$2:C$16,MATCH(2,IF(A$2:A$16=F7,COUNTIFS(A$2:A$16,F7,C$2:C$16,$C$2:$C$16)),0))

In H7 and copy down and copy the range H7:H9 to the ranges J7:J9; L7:L9 and N7:N9

=AVERAGEIFS($D$2:$D$16,$A$2:$A$16,$F7,$C$2:$C$16,"<>"&G7)

In I7 the Array Formula (use Ctrl+Shift+Enter to enter the formula) and copy down and copy the range I7:I9 to the ranges K7:K9 and M7:M9

=INDEX($C$2:$C$16,SMALL(IF(IF($A$2:$A$16=$F7,COUNTIFS($A$2:$A$16,$F7,$C$2:$C$16,$C$2:$C$16))=1,
ROW($A$2:$A$16)-ROW($A$2)+1),CEILING(COLUMNS($I7:I7),2)/2))

In the range H13:J13 the Array Formula (use Ctrl+Shift+Enter to enter the formula) and copy down

=TRANSPOSE(ABS(IFERROR(1/(1/MMULT(IF($G$7:$M$9=F13,$H$7:$N$9,),{1;1;1;1;1;1;1}))-$H$2:$H$4,0)))


[TABLE="class: grid, width: 919"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]candidate[/TD]
[TD]metric[/TD]
[TD]assessor[/TD]
[TD]score[/TD]
[TD][/TD]
[TD]Candidate[/TD]
[TD][/TD]
[TD]Overall Candidate Avg Score[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]1420[/TD]
[TD]M1[/TD]
[TD]YJ[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]1420[/TD]
[TD][/TD]
[TD="align: right"]2,80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1420[/TD]
[TD]M2[/TD]
[TD]MB[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]1452[/TD]
[TD][/TD]
[TD="align: right"]3,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]1420[/TD]
[TD]M3[/TD]
[TD]HG[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]1444[/TD]
[TD][/TD]
[TD="align: right"]2,60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]1420[/TD]
[TD]M4[/TD]
[TD]YJ[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2A[/TD]
[TD][/TD]
[TD]2B[/TD]
[TD][/TD]
[TD]2C[/TD]
[TD][/TD]
[TD]2D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]1420[/TD]
[TD]M5[/TD]
[TD]LT[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]Candidate[/TD]
[TD][/TD]
[TD]Composite Avg1 Exclude Assessor2x[/TD]
[TD][/TD]
[TD]Composite Avg2 Exclude Assessor1x[/TD]
[TD][/TD]
[TD]Composite Avg3 Exclude Assessor1x[/TD]
[TD][/TD]
[TD]Composite Avg4 exclude assessor1x[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]1452[/TD]
[TD]M1[/TD]
[TD]QK[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]1420[/TD]
[TD]YJ[/TD]
[TD="align: right"]3,33[/TD]
[TD]MB[/TD]
[TD="align: right"]2,75[/TD]
[TD]HG[/TD]
[TD="align: right"]2,75[/TD]
[TD]LT[/TD]
[TD="align: right"]2,50[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]1452[/TD]
[TD]M2[/TD]
[TD]QK[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]1452[/TD]
[TD]QK[/TD]
[TD="align: right"]2,33[/TD]
[TD]MD[/TD]
[TD="align: right"]3,50[/TD]
[TD]YJ[/TD]
[TD="align: right"]3,50[/TD]
[TD]MB[/TD]
[TD="align: right"]2,50[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]1452[/TD]
[TD]M3[/TD]
[TD]MD[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1444[/TD]
[TD]BT[/TD]
[TD="align: right"]2,67[/TD]
[TD]DM[/TD]
[TD="align: right"]2,50[/TD]
[TD]RN[/TD]
[TD="align: right"]2,50[/TD]
[TD]MB[/TD]
[TD="align: right"]2,75[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]1452[/TD]
[TD]M4[/TD]
[TD]YJ[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]1452[/TD]
[TD]M5[/TD]
[TD]MB[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Assessor Influence Avg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]1444[/TD]
[TD]M1[/TD]
[TD]BT[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Candidate/Assessor[/TD]
[TD][/TD]
[TD="align: right"]1420[/TD]
[TD="align: right"]1452[/TD]
[TD="align: right"]1444[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]1444[/TD]
[TD]M2[/TD]
[TD]DM[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]BT[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]1444[/TD]
[TD]M3[/TD]
[TD]BT[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]DM[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]1444[/TD]
[TD]M4[/TD]
[TD]RN[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]HG[/TD]
[TD][/TD]
[TD="align: right"]0,05[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]1444[/TD]
[TD]M5[/TD]
[TD]MB[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]LT[/TD]
[TD][/TD]
[TD="align: right"]0,30[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MB[/TD]
[TD][/TD]
[TD="align: right"]0,05[/TD]
[TD="align: right"]0,50[/TD]
[TD="align: right"]0,15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MD[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,50[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]QK[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,67[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]RN[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YJ[/TD]
[TD][/TD]
[TD="align: right"]0,53[/TD]
[TD="align: right"]0,50[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]**[/TD]
[TD]**********[/TD]
[TD]***[/TD]
[TD]**************[/TD]
[TD]***[/TD]
[TD]***************[/TD]
[TD]***[/TD]
[TD]***************[/TD]
[TD]***[/TD]
[TD]***************[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[AdMission]

Phenomenal stuff, Markmzz, thank you.

 

[markmzz]

Phenomenal stuff, Markmzz, thank you.

You're welcome.

Markmzz

 

[AdMission]

I have run into an additional complication; occasionally a set of candidates are assessed by 5 different people, and thus, there are no repeats...just 5 single assessors. How might the formulas derived for H7 and the rest of the exclude assessors columns be modified to accommodate this?

 

[markmzz]

I have run into an additional complication; occasionally a set of candidates are assessed by 5 different people, and thus, there are no repeats...just 5 single assessors. How might the formulas derived for H7 and the rest of the exclude assessors columns be modified to accommodate this?

Hi AdMission!

Try this small modification:

In G7 the Array Formula (use Ctrl+Shift+Enter to enter the formula) and copy down

=IFERROR(INDEX(C$2:C$16,MATCH(2,IF(A$2:A$16=F7,COUNTIFS(A$2:A$16,F7,C$2:C$16,$C$2:$C$16)),0)),"")

In H7 and copy down and copy the range H7:H9 to the ranges J7:J9; L7:L9, N7:N9 and P7:P9

=IF(G7="","",AVERAGEIFS($D$2:$D$16,$A$2:$A$16,$F7,$C$2:$C$16,"<>"&G7))

In I7 the Array Formula (use Ctrl+Shift+Enter to enter the formula) and copy down and copy the range I7:I9 to the ranges K7:K9, M7:M9 and O7:O9

=IFERROR(INDEX($C$2:$C$16,SMALL(IF(IF($A$2:$A$16=$F7,COUNTIFS($A$2:$A$16,$F7,$C$2:$C$16,$C$2:$C$16))=1,
ROW($A$2:$A$16)-ROW($A$2)+1),CEILING(COLUMNS($I7:I7),2)/2)),"")

In the range H13:J13 the Array Formula (use Ctrl+Shift+Enter to enter the formula) and copy down

=TRANSPOSE(ABS(IFERROR(1/(1/MMULT(IF($G$7:$O$9=F13,$H$7:$P$9,),{1;1;1;1;1;1;1;1;1}))-$H$2:$H$4,0)))


[TABLE="class: grid, width: 1086"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]candidate[/TD]
[TD]metric[/TD]
[TD]assessor[/TD]
[TD]score[/TD]
[TD][/TD]
[TD]Candidate[/TD]
[TD][/TD]
[TD]Overall Candidate Avg Score[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]1420[/TD]
[TD]M1[/TD]
[TD]YJ[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]1420[/TD]
[TD][/TD]
[TD="align: right"]2,80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1420[/TD]
[TD]M2[/TD]
[TD]MB[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]1452[/TD]
[TD][/TD]
[TD="align: right"]3,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]1420[/TD]
[TD]M3[/TD]
[TD]HG[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]1444[/TD]
[TD][/TD]
[TD="align: right"]2,60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]1420[/TD]
[TD]M4[/TD]
[TD]YJ[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2A[/TD]
[TD][/TD]
[TD]2B[/TD]
[TD][/TD]
[TD]2C[/TD]
[TD][/TD]
[TD]2D[/TD]
[TD][/TD]
[TD]2D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]1420[/TD]
[TD]M5[/TD]
[TD]LT[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]Candidate[/TD]
[TD][/TD]
[TD]Composite Avg1 Exclude Assessor2x[/TD]
[TD][/TD]
[TD]Composite Avg2 Exclude Assessor1x[/TD]
[TD][/TD]
[TD]Composite Avg3 Exclude Assessor1x[/TD]
[TD][/TD]
[TD]Composite Avg4 exclude assessor1x[/TD]
[TD][/TD]
[TD]Composite Avg5 exclude assessor1x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]1452[/TD]
[TD]M1[/TD]
[TD]QK[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]1420[/TD]
[TD]YJ[/TD]
[TD="align: right"]3,33[/TD]
[TD]MB[/TD]
[TD="align: right"]2,75[/TD]
[TD]HG[/TD]
[TD="align: right"]2,75[/TD]
[TD]LT[/TD]
[TD="align: right"]2,50[/TD]
[TD][/TD]
[TD="align: right"]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]1452[/TD]
[TD]M2[/TD]
[TD]QK[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]1452[/TD]
[TD]QK[/TD]
[TD="align: right"]2,33[/TD]
[TD]MD[/TD]
[TD="align: right"]3,50[/TD]
[TD]YJ[/TD]
[TD="align: right"]3,50[/TD]
[TD]MB[/TD]
[TD="align: right"]2,50[/TD]
[TD][/TD]
[TD="align: right"]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]1452[/TD]
[TD]M3[/TD]
[TD]MD[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1444[/TD]
[TD][/TD]
[TD][/TD]
[TD]QK[/TD]
[TD="align: right"]3,00[/TD]
[TD]DM[/TD]
[TD="align: right"]2,50[/TD]
[TD]BT[/TD]
[TD="align: right"]2,25[/TD]
[TD]RN[/TD]
[TD="align: right"]2,50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]1452[/TD]
[TD]M4[/TD]
[TD]YJ[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]1452[/TD]
[TD]M5[/TD]
[TD]MB[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Assessor Influence Avg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]1444[/TD]
[TD]M1[/TD]
[TD]QK[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Candidate/ Assessor[/TD]
[TD][/TD]
[TD="align: right"]1420[/TD]
[TD="align: right"]1452[/TD]
[TD="align: right"]1444[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]1444[/TD]
[TD]M2[/TD]
[TD]DM[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]BT[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]1444[/TD]
[TD]M3[/TD]
[TD]BT[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]DM[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]1444[/TD]
[TD]M4[/TD]
[TD]RN[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]HG[/TD]
[TD][/TD]
[TD="align: right"]0,05[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]1444[/TD]
[TD]M5[/TD]
[TD]MB[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]LT[/TD]
[TD][/TD]
[TD="align: right"]0,30[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MB[/TD]
[TD][/TD]
[TD="align: right"]0,05[/TD]
[TD="align: right"]0,50[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MD[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,50[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]QK[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,67[/TD]
[TD="align: right"]0,40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]RN[/TD]
[TD][/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0,10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YJ[/TD]
[TD][/TD]
[TD="align: right"]0,53[/TD]
[TD="align: right"]0,50[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]**[/TD]
[TD]**********[/TD]
[TD]***[/TD]
[TD]**************[/TD]
[TD]***[/TD]
[TD]***************[/TD]
[TD]***[/TD]
[TD]***************[/TD]
[TD]***[/TD]
[TD]***************[/TD]
[TD]***[/TD]
[TD]***************[/TD]
[TD]***[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[AdMission]

Hi AdMission!

Try this small modification:

In G7 the Array Formula (use Ctrl+Shift+Enter to enter the formula) and copy down

=IFERROR(INDEX(C$2:C$16,MATCH(2,IF(A$2:A$16=F7,COUNTIFS(A$2:A$16,F7,C$2:C$16,$C$2:$C$16)),0)),"")

In H7 and copy down and copy the range H7:H9 to the ranges J7:J9; L7:L9, N7:N9 and P7:P9

=IF(G7="","",AVERAGEIFS($D$2:$D$16,$A$2:$A$16,$F7,$C$2:$C$16,"<>"&G7))

In I7 the Array Formula (use Ctrl+Shift+Enter to enter the formula) and copy down and copy the range I7:I9 to the ranges K7:K9, M7:M9 and O7:O9

=IFERROR(INDEX($C$2:$C$16,SMALL(IF(IF($A$2:$A$16=$F7,COUNTIFS($A$2:$A$16,$F7,$C$2:$C$16,$C$2:$C$16))=1,
ROW($A$2:$A$16)-ROW($A$2)+1),CEILING(COLUMNS($I7:I7),2)/2)),"")

In the range H13:J13 the Array Formula (use Ctrl+Shift+Enter to enter the formula) and copy down

=TRANSPOSE(ABS(IFERROR(1/(1/MMULT(IF($G$7:$O$9=F13,$H$7:$P$9,),{1;1;1;1;1;1;1;1;1}))-$H$2:$H$4,0)))

Markmzz

My head just exploded....this is better than me on my best day.

thanks.