Participants selected 35 out of 100 squares on 10x10 square grid - Need help calculating mean of all possible pairwise distances between 35 squares

[ea2146]

Hello everyone,

I recently ran an experiment that required participants to select exactly 35 out of 100 squares on a 10x10 grid. I now need to calculate the mean of all possible pairwise distances between the 35 selected squares on the grid.

I have attached images of a file that has two tabs: 1) "responses" and 2) "pairwise distances" Sorry I could not post an actual spreadsheet instead.

The responses tab has participant ID in Column A and square number across Row 1 (i.e., square#1 - square #100). Thus, each row represents a different participant's response pattern across the 100 squares. Each participant was required to select exactly 35 out of the 100 squares. The cell is populated with a "0" if the participant did not select that particular square and a "1" if the participant did select that particular square.

The pairwise distances tab is a 100x100 grid depicting the precise distance between each possible pairwise comparison. For example, if a participant selected square #3 and square #18, then the corresponding distance equals 5.09902. I would love to have a macro or formula that can identify the 35 selected squares for each participant and then, using the data that is populated in the pairwise distances tab, calculate the average of all possible pairwise distance combinations. Specifically, there should 595 different combinations that I want to average for each participant (i.e., for each row of the responses tab) (nCr; when n=35, r=2).

I am completely out of my depth with this one so any help would be greatly appreciated!! Thank you so much in advance!

 

[JGordon11]

Function PADThirtyFive(rng As Range) As Variant

    Dim R As Variant, SelCell(1 To 35, 1 To 2), i As Integer, j As Integer, count As Integer, total As Double
    If rng.Rows.count <> 10 Or rng.Columns.count <> 10 Then
        PADThirtyFive = "Error: non 10x10 range selected"
        Exit Function
    End If
    
    If Application.CountIf(rng, 1) <> 35 Then
        PADThirtyFive = "Error: rng must contain exacly 35 1s"
        Exit Function
    End If
    
    R = rng.Value
    For i = 1 To 10
        For j = 1 To 10
            If R(i, j) = 1 Then
                count = count + 1
                SelCell(count, 1) = i: SelCell(count, 2) = j
            End If
        Next
    Next
    
    For i = 1 To 35
        For j = i + 1 To 35
            total = total + ((SelCell(i, 1) - SelCell(j, 1)) ^ 2 + (SelCell(i, 2) - SelCell(j, 2)) ^ 2) ^ 0.5
        Next j
    Next i
    
    PADThirtyFive = total / 595
    
End Function

 

[ea2146]

Thank you so much, JGordon11! I really appreciate you taking the time to create this for me. I am relatively novice when it comes to using VBA functions in excel. I am not sure how to run the code you created. I am able to create this function in a new VBA module, but I am not sure how to program the function in a way that links my two data tabs. Could you help me with that by any chance? Thanks again!!!

 

[JGordon11]

To use the function put it in a module in the same workbook that your 10x10 are in (sounds like you already did that). Then just type "= PADThirtyFive(B2:K11)" (without the quotes) in an empty cell near the 10x10 grid. Obviously change the B2:K11 to the address where the grid you to analyze is located. You can do this for all the grids you have - it can be called multiple times on worksheet.

MrE20210127.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		0	0	0	0	1	1	0	1	0	0					5.276216343
3		0	0	0	0	0	1	1	1	0	1
4		1	1	1	1	0	1	0	0	0	1
5		0	0	0	0	0	0	0	1	0	1
6		1	0	1	0	1	0	0	0	0	0
7		0	1	1	0	0	0	1	1	0	1
8		1	0	1	0	0	0	1	0	0	1
9		0	0	0	1	0	1	0	0	0	0
10		1	0	0	0	0	0	0	1	1	0
11		0	0	1	1	1	0	0	0	0	0
12
Sheet8

Cell Formulas
Range	Formula
P2	P2	=PADThirtyFive(B2:K11)

 

[ea2146]

This is perfect! Thanks so much. All is working now I really appreciate it.