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
Joined
Apr 9, 2012
Messages
30
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
    responses tab image.JPG
    68.2 KB · Views: 10
  • pairwise distances tab image.JPG
    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
Joined
Jan 18, 2021
Messages
175
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
    
    PADThirtyFive = total / 595
    
End Function
 
Solution

ea2146

New Member
Joined
Apr 9, 2012
Messages
30
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
Joined
Jan 18, 2021
Messages
175
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
P2P2=PADThirtyFive(B2:K11)
 

ea2146

New Member
Joined
Apr 9, 2012
Messages
30
This is perfect! Thanks so much. All is working now :) I really appreciate it.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top