I need help putting together what might be a complex formula. I think I know the conceptual building blocks, but need that last bit of help to bring it all together - or alternatively be given a different approach. First some background.
I am using pwrSIMILARITY
to calculate textual similarity score between any two given input cells, eg.:
Great! However what I am trying to do is calculate multiple comparisons. The spreadsheet I have has multiple columns with similar text. Some of these columns might be blank. For example, out of a possible 17 columns for any given row, 3 may have non-empty text.
pwrSIMILARITY is symmetrical in that the order of input cells produces the same result. I can leverage this in a basic permutation calculation. For example, for 3 input columns, (say C1 to C3) there are just 3 'paired' calculations to perform:
I can then take the average of those to roughly produce a score which I interpret as the overall similarity score for text across columns C1 to C3.
I can use COUNTA and PERMUT to work out the number of pair calculations as follows:
U1=COUNTA(D292:T292) //equals 3
V1=PERMUT(U1,2)/2 //equals 3
The questions are:
How do I get a reference to the cells that COUNTA …