+ Reply to Thread
Results 1 to 4 of 4

Multiplying pairs based on match and then summing result

  1. #1
    Registered User
    Join Date
    09-25-2016
    Location
    Boston, MA
    MS-Off Ver
    excel 2016
    Posts
    3

    Multiplying pairs based on match and then summing result



    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

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Multiplying pairs based on match and then summing result

    That is a formula unfriendly layout!

    Try converting your data to a pivot table which will provide a format that is more practical to work with.

    In sheet2, select all of the data, including the column headers (you will need column headers to do this, if you don't have them, it will not work!). Then on the Excel ribbon, go to the Insert Tab, then click PivotTable, and OK.

    Using the boxes on the right, Drag your column headers to the appropriate boxes, Airline names to Columns, Investor names to Rows, Investment fractions to Values.

    On the Values one, click the dropdown arrow, then 'Value Field Settings', Choose 'Sum' then click OK.

    If you have done it correctly, your airline names will be in B4:D4, with Investment values in B5:D9 (based on the sample data).

    Below the table enter Delta into A14, and American into B14, then enter this formula into C14 to get your result.

    =SUMPRODUCT(INDEX($B$5:$D$9,0,MATCH($A13,$B$4:$D$4,0)),INDEX($B$5:$D$9,0,MATCH($B13,$B$4:$D$4,0)))

    Does that give you enough to work with?

  3. #3
    Registered User
    Join Date
    09-25-2016
    Location
    Boston, MA
    MS-Off Ver
    excel 2016
    Posts
    3

    Re: Multiplying pairs based on match and then summing result

    Wow it works thanks so much. I am about to work on actually applying it to my data. However, I feel like I am going to run into problems because I have more than 100,000 cases with many unique airlines and investors. Should the pivot table work in such conditions? ---I'll get back as soon as I try as well.

  4. #4
    Registered User
    Join Date
    09-25-2016
    Location
    Boston, MA
    MS-Off Ver
    excel 2016
    Posts
    3

    Re: Multiplying pairs based on match and then summing result

    Thanks so much again. Just got it working with my actual data and manually checked to make sure every worked. It worked perfect, thanks so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with the result when I multiplying two numbers???
    By esopi in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 07-27-2016, 05:57 AM
  2. Multiplying by quanitity in parenthesis for COUNT result.
    By mchapa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-22-2016, 06:42 PM
  3. Multiplying then dividing the result of the multiplication
    By cbaker1615 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-08-2010, 04:09 PM
  4. Replies: 4
    Last Post: 12-14-2009, 03:21 PM
  5. differencing sets of numbers, multiplying, and summing
    By stocknewb in forum Excel General
    Replies: 12
    Last Post: 09-02-2008, 09:24 PM
  6. Multiplying and summing attributes that appear in a list
    By Johan_E in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2008, 01:45 AM
  7. Comparing columns then multiplying result by particular cell
    By raehippychick in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-10-2008, 01:39 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1