do a weighted average of a outcome multiple deals multiple periods

Dear Sir/Madam,
I have a data set which contains information between buyers and sellers for a certain period of time. I have multiple buyers, multiple sellers and multiple times. For each buyer, there are situations where the given buyer has more than one deals with the same seller at a given time.

I need to generate a weighted average of the price for a given pair of trading relationship at a given moment of time (e.g year). The weight I am using is the amount of trade for each deal between the same pair at a given moment of time divided the total amount of all deals for the pair at the same moment of time.

for example, buyer A and seller B have 3 deals in day one. The first deal is 20 apples and the price is 1 dollar each apple. the second deal is 30 apples and the price is 1.1 dollar each apple and the third deal is 25 apples and the price is 0.9 each apple. I am intended to calculate the weighted average price of apple for A and B in day one as follows: (20/(20+30+25))*1+(30/(20+30+25))*1.1+(25/(20+30+25))*0.9


I understand I can use excel comment such as sumif/sumifs together with others to achieve what I want. But my case in question is more complicated since I have quite large number of buyers, quite large number of sellers, quite large number of times and high proportion of deals between the same pair for a given moment of time.

I hope to get your help by using VBA.

I enclose a hypothetical example of my data set here.

Thank you for your help and I hope to hear from you soon.

Tianshu

You will need to provide some expected results as well as sample source data.

No, sorry - please add some expected results to the attached workbook. Thanks.

Dear Leo and other friends in the forum,

I further adjusted my data in the spreadsheet. Now the first worksheet contains the raw data and the second one contains the manipulation steps and the expected results I hope to get after the manipulation.

Thanks in advance.

Tianshu