google-sheets - 从列表中获取 Google 表格中的平均股票价格

Question

我正在尝试从动态列表中获取平均股票价格。

在我的示例中，我在不同的几天内购买了股票。我已经卖掉了其中的一些(不是全部)。所以，我有一些可用的股票，我想找出平均价格:

如何在不手动更改列表的情况下从较早日期到最新日期从未售出的股票中获取平均价格？

手动，结果必须是:$19,82

我构建了一个 example sheet .

谢谢!任何帮助将不胜感激!

Answer 1

使用:

=AVERAGE.WEIGHTED(FILTER(E3:E6; B3:B6="buy"); FILTER(D3:D6; B3:B6="buy"))

---

更新 1:

=INDEX(AVERAGE.WEIGHTED(FILTER(E3:E6; B3:B6="buy"); FILTER(D3:D6; B3:B6="buy")-
 {SEQUENCE(COUNTIF(B3:B6; "buy")-1; 1; 0; 0); FILTER(D3:D6; B3:B6="sell")}))

---

更新2:

=ARRAYFORMULA(AVERAGE.WEIGHTED(FILTER(E3:E6; B3:B6="buy"); IF(0<FILTER(D3:D6; B3:B6="buy")+
 IF(0>MMULT(TRANSPOSE((SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))>=
 TRANSPOSE(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))))*
 QUERY({FILTER(D3:D6; B3:B6="buy");-SUM(FILTER(D3:D6; B3:B6="sell"))}; "offset 1"; 0)); 
 SIGN(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy"))))); 
 MMULT(TRANSPOSE((SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))>=
 TRANSPOSE(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))))*
 QUERY({FILTER(D3:D6; B3:B6="buy");-SUM(FILTER(D3:D6; B3:B6="sell"))}; "offset 1"; 0)); 
 SIGN(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy"))))); 0); FILTER(D3:D6; B3:B6="buy")+
 IF(0>MMULT(TRANSPOSE((SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))>=
 TRANSPOSE(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))))*
 QUERY({FILTER(D3:D6; B3:B6="buy");-SUM(FILTER(D3:D6; B3:B6="sell"))}; "offset 1"; 0)); 
 SIGN(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy"))))); 
 MMULT(TRANSPOSE((SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))>=
 TRANSPOSE(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))))*
 QUERY({FILTER(D3:D6; B3:B6="buy");-SUM(FILTER(D3:D6; B3:B6="sell"))}; "offset 1"; 0)); 
 SIGN(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy"))))); 0); 0)))