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

#### ea2146

##### New Member
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!

#### Attachments

• responses tab image.JPG
68.2 KB · Views: 10
• pairwise distances tab image.JPG
150.7 KB · Views: 10

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### JGordon11

##### Board Regular
Rich (BB code):
``````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

End Function``````

#### ea2146

##### New Member
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

##### Board Regular
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
ABCDEFGHIJKLMNOP
1
200001101005.276216343
30000011101
41111010001
50000000101
61010100000
70110001101
81010001001
90001010000
101000000110
110011100000
12
Sheet8
Cell Formulas
RangeFormula

#### ea2146

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

1,141,863
Messages
5,709,063
Members
421,611
Latest member
Lisa W

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back