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!

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!