Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare ranges - compute agreement %
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 . . . |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare ranges - compute agreement %
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 -- Gary''s Student - gsnu200840 "andy62" wrote: 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 . . . |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare ranges - compute agreement %
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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200840 "andy62" wrote: 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 . . . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare ranges - compute agreement %
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!B3:D14=Rater3!B3:D14), --(Rater1!B3:D14=Rater2!B3:D14), --(Rater2!B3:D14=Rater3!B3:D14)) It's get a bit long with four arrays (six sets of comparisons) but it'll work. Thanks! "andy62" wrote: 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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200840 "andy62" wrote: 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 . . . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare ranges - compute agreement %
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! -- Gary''s Student - gsnu200840 "andy62" wrote: 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!B3:D14=Rater3!B3:D14), --(Rater1!B3:D14=Rater2!B3:D14), --(Rater2!B3:D14=Rater3!B3:D14)) It's get a bit long with four arrays (six sets of comparisons) but it'll work. Thanks! "andy62" wrote: 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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200840 "andy62" wrote: 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 . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare named ranges | Excel Worksheet Functions | |||
compare date ranges to set up eq | Excel Worksheet Functions | |||
compare two columns with different ranges in two worksheets | Excel Discussion (Misc queries) | |||
Compare ranges different size | Excel Worksheet Functions |