Calculating Agreement between various columns into a Matrix

Hello, I've been in this forum once before, but am still quite a novice at excel (compared to most folks here), so I'm hoping my questions make sense and can lead to some progress with my work.

I'm working on collating different Greek editions of the New Testament (18 editions to be exact) -- "collating" means I am finding points of variation between those texts, and marking down which editions have which 'readings'. I'm using excel to track them at each point of variation, and then compare them against each other.

Variants1.PNG

In the image above I'm showing an brief example. This would be the book of John, and I numbered each variant as "variant 1", etc. For each variant, I gave all the readings which that variant has a number (1, 2, etc.), then for each edition I marked the reading it used in its text.

So, here's what I'm trying to do with this:

Matrix1.PNG

I want to make a matrix showing the percentage of agreement between each PAIR of editions. Since there are 18 editions used in this, that would make 324 pairs. It seems to me there are two steps in the calculation: 1) calculating the agreement between the two on every variant, and 2) dividing the # of agreements over the total # of variants. What I'm trying to figure out is - how to calculate the agreement between EACH EDITION on every variant (every row). Then, having all of those agreements added up down the entire sheet, and inserted into the matrix spreadsheet. Then have them divided by the total # of variants to get the percentage. To imagine doing this for every single variant 324 times seems far too tedious, and I'm hoping there is some easier way in excel to go about it.

I'm not sure if that was 100% clear, but if you're unsure what I mean let me know and I'll try to clarify. This is a tough project, as I have to examine 18 books at once looking for any minute difference, and to add the difficulty of excel to it just makes my head want to explode. But I'm hoping that someone out there is able to help me out!

Thank you in advance.

Hi Greg, thanks! It looks like you understood what I wanted pretty well. And I really appreciate you giving this a try. I'm sure it took a good bit of time putting that together.

I tried to look up the functions you were using so I could understand it, (even though I didn't understand it all completely) it seems to me what you're doing is matching the names of each group (in the example: "E1" "E2" etc.) with the same names of each column in the above chart, then adding the number of times each group equal's each other (row by row), then dividing that by the total number of variants (using the count function).

I think that's what's going on. But as I tried to apply it to another chart by following the same formula, I ended up with errors. I'm trying to actually understand it because I will add more data to it over time, and will have to be able to adapt the formula to other information. I think my errors occurred because I used a different sheet, and cells that I was trying to reference were in other places, and somewhere I was missing something. I don't think I was referencing the information in the chart correctly.

I used the same spreadsheet you sent and added 2 more tabs to it which contain the specific work I'm doing. Sheet2 is a direct copy of stuff I had done so far. The difference with that sheet is, it was originally designed just to calculate the difference between the Greek NT we compiled with 17 others. But after I got started, it was decided we also wanted to compare them all with each other, to see which ones cohere best with each other. So all the agreement/disagreement data on that page is only how the CHGNT itself compares with the others. Not how ALL of them compare. So I was put in a bind by this decision. All that is relevant on that page to what I'm asking is the "Reading No." columns (that is what tells us where they agree or disagree).

I made another sheet (sheet3) which only has the "Reading No." columns filtered, in case this task just isn't possible to do on the original sheet I started with. You can tell me if it is or isn't. But if not, I can filter it like this to make it simpler. That is the sheet I was using to try and adapt your formula to, and ended up with errors.

So, it seems what you started with is very much on the right track, but I just haven't been able to apply it beyond the example you gave. If it's not too much trouble for you, I'd be so grateful to learn or figure out how to get it that to work on more information, and where I can adapt it to more over time.

Let me know if you can come up with anything. And either way, thank you at least for trying.

Greg -- that looks amazing! At first I wasn't sure how to add more lines... but then I dragged the blue box down to cover the additional rows -- and the matrix then recalculated with the additional lines included. So it looks to me like everything works just as needed! I also added an additional column to see how that works, and was able to add one with no problems (thanks to your help).

I attached the spreadsheet with the additional rows/columns. The only further question I have left is related to the filter which was added to the top of the columns. When I saw those, I was wondering if there was a way to add a filter to the chapter column, where I can filter particular chapters out of the book, and then the matrix will calculate ONLY the data for that chapter? When I used the filter on one of the columns, it filtered the info, but didn't adjust the data in the matrix. I was curious if that was possible or not. Researching it on my own today gave me the impression it would take some further restructuring of the whole thing -- which if so, I'll forget about it.

**On topic of the filter question -- I think what I'm gonna do is have the sheet you created (with the matrix) be at the back of the workbook, and create a front sheet which features the matrix table in the center (I will funnel the data through to the cells on the front sheet), so that I can add other average/sum data relevant to it in the same place and have it all featured at the front. And therefore -- if there is to be a filter added on the 'chapter' column -- is it possible for that filter to work through a drop-down or check-box list on the front sheet (or does it need to be on the 'chapter' cell at the head of the table on the back sheet)? Of course, if the filter itself I'm proposing cannot be implemented at all, then this additional question is irrelevant. But if the filter can be made, is there a way the filter criteria selection be done on a different sheet through a drop-down or check-box list?

As I said, you've worked wonders with my issues on this project, and I can't be more appreciative of your help!