A
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 . . .
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 . . .