Creating a Network Chart with Nodes and Links

[DenkoB]

I'm trying to chart a diagram of nodes and links in a social network - who talks to whom within an organization.

To take a simple example, if there are 2 people and they talk to each other, there would be 1 link; 3 people who talk to each other, 3 links; 4 people, 6 links; 5 people, 10 links, etc. (The formula when everyone is linked is n(n-1) / 2, where n is the number of people in the group.) If some people don't talk to each other, of course, the number of links would be smaller.

I can create a diagram like this manually in Excel, but only painstakingly. To do it, I have to create an xy graph with lines connecting the points and make sure that every point is adjacent to every other point that it is linked to.

So for instance for a diagram with 4 points (a, b, c and d) and 6 links, I plot the data below (there are some double links, but they overlay on the graph so you don't see them). If I just graphed a, b, c and d, Excel would interpret it as a time series. In the columns below, every point is adjacent to every other point (for instance, a is adjacent to b, c and d), so lines are drawn from a to all other 3 points.

a 9 9
d 0 0
c 0 9
b 9 0
a 9 9
a 9 9
c 0 9
d 0 0
b 9 0

My question: Is there a way to do this more easily? I want a user to be able to indicate where there is a link and then have Excel plot that link. So for instance data laid out like so...

b c d
a 1 1 1
b 1 1
c 1

...would indicate that there are links between a and b, c, d; between b and c, d; and between c and d. Can anyone advise me on a way to make this task simpler?

Thanks.

Dan Armstrong

 

[tusharm]

Interesting problem.

Here's a solution in which the graph will update itself automatically. In exchange, it requires some amount of up-front setup.

First, enter the following user-defined-function in a standard module [the first few functions are support functions needed by the SeriesVals UDF.]

Option Explicit
    Function ArrLen(x, Optional whatDimension As Long = 1)
        ArrLen = UBound(x, whatDimension) - LBound(x, whatDimension) + 1
        End Function
    Function Pi()
        Pi = Application.WorksheetFunction.Pi()
        End Function
    Function XCoord(ThisPersonNbr, NbrPersons)
        XCoord = Cos((ThisPersonNbr - 1) * 2 * Pi() / NbrPersons)
        End Function
    Function YCoord(ThisPersonNbr, NbrPersons)
        YCoord = Sin((ThisPersonNbr - 1) * 2 * Pi() / NbrPersons)
        End Function
Function SeriesVals(NbrPersons As Long, Connections)
    'Minimal data integrity checks.  Will crash if data are poorly defined.
    Dim myConn As Variant
    If TypeOf Connections Is Range Then
        If Connections.Cells.Count > 1 Then
            'Data assumed to be in a single row.  Will crash if range _
             is columnar!
            myConn = Application.WorksheetFunction.Transpose(Connections.Value)
        Else
            ReDim myConn(1 To 1, 1 To 1)
            myConn(1, 1) = Connections.Value
            End If
    Else
        myConn = Connections    'No idea what this will do
        End If
    ReDim Rslt(1 To (UBound(myConn) - LBound(myConn) + 1) * 2, 1 To 2) As Double
    Dim ThisPersonNbr As Long, i As Long
    ThisPersonNbr = NbrPersons - ArrLen(myConn)
    For i = LBound(Rslt, 1) To UBound(Rslt, 1) Step 2
        Rslt(i, 1) = XCoord(ThisPersonNbr, NbrPersons)
        Rslt(i, 2) = YCoord(ThisPersonNbr, NbrPersons)
        Next i
    For i = LBound(Rslt, 1) + 1 To UBound(Rslt, 1) Step 2
        If myConn(i \ 2, 1) = 1 Then
            Rslt(i, 1) = XCoord(ThisPersonNbr + (i \ 2), NbrPersons)
            Rslt(i, 2) = YCoord(ThisPersonNbr + (i \ 2), NbrPersons)
        Else
            Rslt(i, 1) = Rslt(i - 1, 1)
            Rslt(i, 2) = Rslt(i - 1, 2)
            End If
        Next i
    SeriesVals = Rslt
    End Function

For the setup of the worksheet, I'll illustrate with a map for 5 people, a, b, c, d, and e.

In A2:A5 enter a, b, c, and d. In B1:E1, enter b, c, d, e.

In B2:E5 enter a map of 1's and 0's. It is your responsibility to ensure this range contains data only in the upper triangle.

Leave some number of columns blank for future expansion. So, in J1, enter the total number of people in the network. Use the formula =COUNTA(A:A)+1

Leaving alternative columns empty in K1:Q1 enter the letters a, b, c, and d.

Select K2:L{n} where n =1+2*(M-1), where M is the largest number of possible participants in the network. So, if the network is likely to contain 8 participants, n will be 1+2*(8-1)=15

Array-enter the formula =SeriesVals($J$1,OFFSET($B$2,(COLUMN(K2)-COLUMN($K$2))/2,(COLUMN(K2)-COLUMN($K$2))/2,1,$J$1-1-(COLUMN(K2)-COLUMN($K$2))/2))
[Note: To array-enter a formula, complete data entry with CTRL+SHIFT+ENTER rather than just ENTER.]

Copy this range of cells to each pair of columns in M:R

Select K2:L{n} and create a XY Scatter chart (subtype the one with straight lines connecting the points). Select each subsequent pair of columns in M:R, and drag them onto the chart. In the dialog box that pops up, ensure you check 'first column contains x values'.

Label the nodes as desired. Now, when you change the connection map, the chart will update automatically. You can also add new people to the network. Just remember to add an additional pair of columns for each person (to the right of column R in the above example) and add this new pair to the chart.

Some more stuff: Format the chart as desired. Set the min. and max. values for each of the two axes to -1 and +1 respectively. Remove the axis (double-click the axis and then the Patterns tab) and adjust the plotarea to achieve an appropriate symmetric display.

 

[icyrius]

I really tried to build this spreadsheet following the instructions provided here without successfully building the network chart.

Has someone successfully built this chart? I am stuck at the point of the long formula provided in the thread.


Thanks.

Interesting problem.

Here's a solution in which the graph will update itself automatically. In exchange, it requires some amount of up-front setup.

First, enter the following user-defined-function in a standard module [the first few functions are support functions needed by the SeriesVals UDF.]

Option Explicit
    Function ArrLen(x, Optional whatDimension As Long = 1)
        ArrLen = UBound(x, whatDimension) - LBound(x, whatDimension) + 1
        End Function
    Function Pi()
        Pi = Application.WorksheetFunction.Pi()
        End Function
    Function XCoord(ThisPersonNbr, NbrPersons)
        XCoord = Cos((ThisPersonNbr - 1) * 2 * Pi() / NbrPersons)
        End Function
    Function YCoord(ThisPersonNbr, NbrPersons)
        YCoord = Sin((ThisPersonNbr - 1) * 2 * Pi() / NbrPersons)
        End Function
Function SeriesVals(NbrPersons As Long, Connections)
    'Minimal data integrity checks.  Will crash if data are poorly defined.
    Dim myConn As Variant
    If TypeOf Connections Is Range Then
        If Connections.Cells.Count > 1 Then
            'Data assumed to be in a single row.  Will crash if range _
             is columnar!
            myConn = Application.WorksheetFunction.Transpose(Connections.Value)
        Else
            ReDim myConn(1 To 1, 1 To 1)
            myConn(1, 1) = Connections.Value
            End If
    Else
        myConn = Connections    'No idea what this will do
        End If
    ReDim Rslt(1 To (UBound(myConn) - LBound(myConn) + 1) * 2, 1 To 2) As Double
    Dim ThisPersonNbr As Long, i As Long
    ThisPersonNbr = NbrPersons - ArrLen(myConn)
    For i = LBound(Rslt, 1) To UBound(Rslt, 1) Step 2
        Rslt(i, 1) = XCoord(ThisPersonNbr, NbrPersons)
        Rslt(i, 2) = YCoord(ThisPersonNbr, NbrPersons)
        Next i
    For i = LBound(Rslt, 1) + 1 To UBound(Rslt, 1) Step 2
        If myConn(i \ 2, 1) = 1 Then
            Rslt(i, 1) = XCoord(ThisPersonNbr + (i \ 2), NbrPersons)
            Rslt(i, 2) = YCoord(ThisPersonNbr + (i \ 2), NbrPersons)
        Else
            Rslt(i, 1) = Rslt(i - 1, 1)
            Rslt(i, 2) = Rslt(i - 1, 2)
            End If
        Next i
    SeriesVals = Rslt
    End Function

For the setup of the worksheet, I'll illustrate with a map for 5 people, a, b, c, d, and e.

In A2:A5 enter a, b, c, and d. In B1:E1, enter b, c, d, e.

In B2:E5 enter a map of 1's and 0's. It is your responsibility to ensure this range contains data only in the upper triangle.

Leave some number of columns blank for future expansion. So, in J1, enter the total number of people in the network. Use the formula =COUNTA(A:A)+1

Leaving alternative columns empty in K1:Q1 enter the letters a, b, c, and d.

Select K2:L{n} where n =1+2*(M-1), where M is the largest number of possible participants in the network. So, if the network is likely to contain 8 participants, n will be 1+2*(8-1)=15

Array-enter the formula =SeriesVals($J$1,OFFSET($B$2,(COLUMN(K2)-COLUMN($K$2))/2,(COLUMN(K2)-COLUMN($K$2))/2,1,$J$1-1-(COLUMN(K2)-COLUMN($K$2))/2))
[Note: To array-enter a formula, complete data entry with CTRL+SHIFT+ENTER rather than just ENTER.]

Copy this range of cells to each pair of columns in M:R

Select K2:L{n} and create a XY Scatter chart (subtype the one with straight lines connecting the points). Select each subsequent pair of columns in M:R, and drag them onto the chart. In the dialog box that pops up, ensure you check 'first column contains x values'.

Label the nodes as desired. Now, when you change the connection map, the chart will update automatically. You can also add new people to the network. Just remember to add an additional pair of columns for each person (to the right of column R in the above example) and add this new pair to the chart.

Some more stuff: Format the chart as desired. Set the min. and max. values for each of the two axes to -1 and +1 respectively. Remove the axis (double-click the axis and then the Patterns tab) and adjust the plotarea to achieve an appropriate symmetric display.