Forum Discussion
Compare 3 sets of data and display percentage of new entries
Hello,
we are running 3 surveys and we would like to compare how many new participants we are getting each time. We have 3 sets of email addresses from each survey.
We have following sets of data (~2000 emails each set)
Set 1:
1@email.com
2@email.com
3@email.com
4@email.com
Set 2:
1@email.com
2@email.com
5@email.com
6@email.com
Set 3:
1@email.com
2@email.com
5@email.com
8@email.com
Result we would like to get:
In data set 2 we have 50% new participants comparing with set 1
In data set 3 we have 50% new participants comparing with set 1 and 25% new participants comparing with set 2
I will really appreciate your help on that,
Thanks!
Hi Vadim,
Let say your sets are in columns A, B and C. To calculate number of records in each set you may as
=COUNTA($A$1:$A$2000)
and how many records from set 1 are in set 2 as
=SUM(COUNTIF($A$1:$A$2000,$B$1:$B$2000))
above is array formula (Ctrl+Shift+Enter)
The rest is simple arithmetic.
You may use above formulas for entire columns, like
=SUM(COUNTIF($A:$A,$B:$B))
but it will take minutes to recalculate
Hello Sergei,
For some reason I'm getting "0" as a result of this formula
=SUM(COUNTIF($B$1:$B$2000,$D$1:$D$2000))
In my case data set 1 is in range B:B, and data set 2 is in range D:D
Vadim, it looks like this and attached
Be sure you enter the formula as array one, i.e. by combination of Ctrl+Shift+Enter
