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>
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,319
Messages
5,510,563
Members
408,800
Latest member
Realexcel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top