Compare ranges - compute agreement %

[andy62]

I have four identically structured worksheets with responses to a survey. I
need to produce a statistic on how frequently the respondents agreed with
each other (i.e., gave the same answer). The statistics I need isn't per
question, it's by sets of questions, so in essense I am comparing four ranges
that are each 3 X 12 cells. Without adding helper columns or using a pivot
table, is there any kind of array formula that can compute the degree of
agreement in the 36-cell range across the four worksheets? By degree of
agreement I mean the number of times that all four cells A1 match plus the
number of times all four cells A2 match etc. etc., divided by 36 which is the
total number of possible matches. Iwould get a statistic like 50% if there
was a four-way match among 18 cells in the four 36-cell ranges.

To the degree this helps, all the data is 1's or 0's. So in each test for a
match I could look for sums of 4 or 0.

TIA. This cannot be an easy one . . .

 

[Gary''s Student]

Here is a simple example using two tables, you can expand to four tables:

In Sheet1 cells A1 thru C3:

1 4 1
2 3 3
3 3 3

In Sheet2 cells A1 thru C3:

1 6 1
6 3 6
3 3 3

Clearly six of the 9 cells agree. The formula is:

=SUMPRODUCT(--(Sheet1!A1:C3=Sheet2!A1:C3))/9

 

[andy62]

Hi Gary"s - Got that for 2 arrays, but what do you do when it's 3? This kind
of thing doesn't work:

=SUMPRODUCT(--(Sheet1!A1:C3=Sheet2!A1:C3=Sheet3!A1:C3))

And I can't sum across the three pairwise comparisons (1:2, 1:3, 2:3)
because I only want to count when all three agree.

TIA

Since the data is 1's and 0's, another nice function that works - but only
on two arrays - is:

=SUMXMY2(Rater1!$B$3:$D$14,Rater3!$B$3:$D$14)

- but note that this counts DIFFERENCES, so you have to subtract from the
total n.

 

[andy62]

Okay, yeah, I could've thought a bit before I responded. To expand to three
arrays I think you mean something like this:

=SUMPRODUCT(--(Rater1!B314=Rater3!B314),
--(Rater1!B314=Rater2!B314), --(Rater2!B314=Rater3!B314))

It's get a bit long with four arrays (six sets of comparisons) but it'll
work. Thanks!

 

[Gary''s Student]

Think about this alternative. It is easy to sum a single cell across many
worksheets. It is just as easy to get the MAX or MIN across many sheets.
Even if we had 100 worksheets, if the MAX = MIN, then the cell must have the
same value across 100 sheets. This might be easier than many, many
comparisons.

Good luck with this!