1
For a research, I have the following dataset in Excel (simplified for illustration purposes):
Article | Boolean1 Boolean2 Boolean3 Boolean4|
--------------------------------------------------------|
1 | Yes No Yes No |
2 | No Yes No Yes |
3 | Yes No No Yes |
4 | Yes Yes No Yes |
5 | Yes No No No |
I want to calculate the Jaccard Index for each possible combination of booleans, and thus first make a large crosstab worksheet that looks something like this:
| Boolean2 No Boolean2 Yes | Boolean3 No Boolean3 Yes |
-----------------------------------------------------------------------|
Boolean1 No | 0 1 | 1 0 |
Boolean1 Yes | 3 1 | 3 1 |
-----------------------------------------------------------------------|
Boolean4 No | 2 0 | 1 3 |
Boolean4 Yes | 1 2 | 1 0 |
However, I want all combinations in such a table, so in this case boolean1-boolean2, boolean1-boolean3, boolean1-boolean4, boolean2-boolean3, boolean2-boolean4, and boolean3-boolean4 (1-1, 2-2, etc. I can filter out myself).
Now, this could be done by using the 'Crosstabs' on all combinations in SPSS or making a PivotTable in Excel for every possible combination; however, as I have 144 articles and 29 variables (all boolean), this would be quite time-consuming to copy to a separate worksheet. As PivotTables 'stack' the variables, putting all variables in the same rows/columns in a PivotTable also doesn't make sense. The answer that came closest to what I was looking for was this answer; however, it did not work for me on Excel 2016.
Is there any way to create a giant table containing all possible crosstabs more efficiently than copying manually to a new or existing worksheet in Excel, or perhaps in another tool like SPSS?
Dear Peter, thank you for your response. However, the formula doesn't work as it gives the popup of "not trying to type a formula?". I presume there's just a syntax error in there, as the INDIRECT reference doesn't seem to work; however, the formula is too syntactically difficult for me to understand exactly what is going on (sorry, I'm a noob). – Duc – 2018-11-13T11:27:29.353
Depending on your region, you have to replace the
;
(semicolon) with a,
(comma). Other than that, which part of the formula do you not understand ? – Peter K. – 2018-11-13T13:02:35.883