This additional bit tells excel to multiply the values in column B and D of each returned row found above and SUMPRODUCT adds each result together. This bit returns the rows from column A where it equals to the row's CUSIP. Let me know if this works for you! I tried it on your sample data and it seems to be working.
Put a filter and remove all the rows where the total quantity and average price is blank, and sort to make the worksheet neat. The first formula gives you the total quantity for the CUSIP and the second gives you the average price.Ĭopy and paste values for those two columns after calculation. Put these in cell D2 and E2 respectively (which is the next column after Price and in the row of CUSIP AF0).Īnd fill to the bottom of the worksheet to get the weighted average price or each CUSIP. Okay, after quite some research (interesting question by the way!), I came up with this: =IF(COUNTIF($A$2:A2,A2)>1,"",SUMIF(A:A,A2,B:B))
0 kommentar(er)
