Multi-variable averages of select data

New Member
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.

 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>

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

mopp1

Board Regular
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

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

New Member
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

Board Regular
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).

Last edited:

Light bulb!!

Replies
8
Views
672

1,148,294
Messages
5,745,944
Members
423,985
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back