Multi-variable averages of select data

AdMission

New Member
Joined
Mar 22, 2018
Messages
15
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.

CandidatesComposite ScoreCandidateAssessorExclude Score1AssessorExclude Score2AssessorExclude Score3AssessorExclude Score4AssessorExclude Score5AssessorExclude Score6
AA2.857AAYJ3.2MB2.833NA2.833LT2.667
AB3.286ABQK3LT3.667GS3.667FR3.000
AC2.429ACVL2.667MB2.333TB2.167NA2.333BS2.667
AD1.714ADBK1.500HK1.833LL1.833MG1.833MA1.667
AE1.714AEMA1.6RS1.833DM1.667HK1.500
AF1.286AFVL1.2TB1.167MA1.333MG1.333
AG2.714AGBL2.4JJ2.667BK2.500LT2.833
AI2.429AIFA2.4PK2.167LE2.333MB2.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
Joined
Apr 4, 2017
Messages
74
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

New Member
Joined
Mar 22, 2018
Messages
15
Works like an effing charm!! Thanks to you and all others who provide such amazing brainpower.
 

AdMission

New Member
Joined
Mar 22, 2018
Messages
15
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
Joined
Apr 4, 2017
Messages
74
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:

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top