Hey peeps. I am not smart enough to figure this out, and I really could use some help.
The data is in in sheet2. The actual data had 100,000s of cases, but I have given a simplified version of the data is the same conceptually. Each case records an airline, an investor, and the fraction of shares owned by the each investor.
The cases in in sheet 1 provide the information needed to calculate the answers I am looking for. Each case represents a unique pairing of airlines.
What I am looking to do is for each pair of airline multiply the fraction of shares of any investor in both airlines and then add all of those values.
For example, here is how the first case should be calculated (the pairing of Delta and American as shown in case one). Delta and American share two investors Vanguard and Blackrock. Vanguard owns .302 in delta and .255 in American. While Blackrock owns .205 in Delta and .506 in American. Therefore the answer for Delta American that I have looking for is calculated as (.302*.255)+(.205*.506) returning the value of 0.18074 which should be recorded in C1 on sheet1.
I would like a formula is C1 which I could copy for all values.
I have been struggling with this for a long time and haven’t really gotten anywhere. Please any help would be greatly appreciated.
Sheet1
A B C
Delta American Answer
Delta Southwest Answer
American Southwest Answer
Sheet2
A B C
Delta Blackrock 0.205
Delta Vanguard 0.302
Delta Toshi 0.305
American Vanguard 0.255
American Blackrock 0.506
American Tralf 0.123
Southwest Tralf 0.25599
Southwest Indigo 0.4534
Bookmarks