Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have some email network data in Excel as a edgelist meaning I have columns Vertex1, Vertex2, and then N columns of properties of that edge like how many emails were sent from one person to another. For each row in the data, Vertex1 is the source of a message, and Vertex2 is the target, so edges are directed. Here's some sample data

Vertex1 Vertex2 nMessages
Bob     Cindy   12
Cindy   Bob     3
Bob     Mike    11
Cindy   Mike    1

I'm trying to calculate a geometric mean of the form

gm = sqrt[(# of edges ij)*(# of edges ji)]

So gm for Bob and Cindy is gm = sqrt[(messages from Bob to Cindy)*(messages from Cindy to Bob)] or sqrt(12*3) = 6. Is there a way to make that a formula in Excel?

share|improve this question
    
Have you tried indexing the cells to get the names, then using one of the Lookup function to pick up the values? –  user1161318 Nov 28 '12 at 18:42
    
I'm not sure what you mean. I thought about using VLOOKUP, but a sender or receiver could occur more than once. How could I use INDEX and VLOOKUP together? It looks like INDEX uses just one reference, but really I have two (Bob and Cindy, not just Bob or Cindy). –  Libby Nov 28 '12 at 18:57
    
Do the sender and receiver occur exactly twice (eg: [Bob,Cindy] & [Cindy,Bob]), at most twice, or more than twice? –  user1161318 Nov 28 '12 at 19:01
    
At most twice. The pair can not exist (no messages in either direction) or exist once (messages in one direction) or exist twice (messages in both directions). –  Libby Nov 28 '12 at 19:10
    
Got it. Try this: office.microsoft.com/en-us/excel-help/… –  user1161318 Nov 28 '12 at 19:15
add comment

migrated from stackoverflow.com Nov 29 '12 at 16:27

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 3 down vote accepted

I can do it with a helper column. (with Excel 2007 or above, I don't need the helper column, I can post that answer if you prefer)

Assuming your example data is in A,B and C:

in D2, put =A2&B2, and copy that down the data

In E1, put the first name
in E2, put the second name
in E3, put the formula =SQRT(SUMIF(D2:D5,E1&E2,C2:C5)*SUMIF(D2:D5,E2&E1,C2:C5))

the SumIf checks the names, first in the order given, and adds up the number of emails, and second, in reverse order, and adds up the number.

Note that this will give zero if emails only go in one direction, or if no emails go between either person.

share|improve this answer
    
Nice answer @Sean –  John Bustos Nov 28 '12 at 19:28
    
Thanks @Sean! I'm using Excel 2010 but don't mind using a helper column. –  Libby Nov 28 '12 at 19:34
    
Much more elegant than what I was thinking which involved lookups and indexes! Kudos! –  user1161318 Nov 28 '12 at 21:18
add comment

Without a helper column and for all xl versions

=GEOMEAN(SUMPRODUCT(--(A1:A4="Bob"),--(B1:B4="Cindy"),C1:C4),SUMPRODUCT(--(A1:A4="Cindy"),--(B1:B4="Bob"),C1:C4))

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.