lundi 2 septembre 2019

How to get average stock price from an array?

I'm trying to get average stock prices from a dynamic list.

In my example, I've made a bought stocks in a few different days. And I've sold some of them (not all). So, I have some stocks available that I want to find out the average price:

 +------------+---------+------------+---+----------+-------------+
 | date       | buy qty |  buy price |   | sell qty | sell price  |
 +------------+---------+------------+---+----------+-------------+
 | 08/30/2019 |  259    |    $86.02  |   |    0     |     $0.00   |
 | 08/29/2019 |  22     |    $85.77  |   |    0     |     $0.00   |
 | 08/28/2019 |  18     |    $84.22  |   |    0     |     $0.00   |
 | 08/27/2019 |  30     |    $84.00  |   |    0     |     $0.00   |
 | 08/26/2019 |  44     |    $85.76  |   |    6     |    $86.29   |
 | 08/25/2019 |  14     |    $85.77  |   |   14     |    $86.21   |
 +------------+---------+------------+---+----------+-------------+

Available stocks: 367

How to get an average price from stocks not sold, from the earlier to the latest date?

Manually, the result must be: $85.72

I've built an example sheet.

Any help will be appreciated!

Aucun commentaire:

Enregistrer un commentaire