Средняя таблица Excel с приоритезацией

Question

Мне нужна помощь, как написать формулу Excel (или любое решение Excel), чтобы подвести итог с приоритетами.

У меня есть данные транзакции, как это:

Date  Unit  Price  Buy/Sell 
7/11   3     10     Buy 
7/12   2      9     Sell
7/13   4      8     Buy 
7/14   3     11     Sell

Я хочу рассчитать среднюю цену покупки для единиц в руке, а не для всех купленных единиц. У меня в руках было 2 единицы, напомним FIFO, поэтому в среднем будет 8 долларов.

3 единицы по 10 долларов и 4 единицы по 8 долларов были куплены, а первые 5 единиц были проданы.

Любые идеи, чтобы решить эту проблему в Excel?

Answer 1

Нет простого способа сделать это с помощью функций рабочего листа, потому что вы должны отслеживать список покупных цен произвольной длины, который со временем меняется.

Предполагая, что число единиц является положительным целым числом, я могу сделать это с помощью вспомогательного столбца. Допустим, данные находятся в столбцах AD, а вспомогательный столбец - E.

В E2 положить =IF(D2="Buy",E1&REPT(C2&",",B2),MID(E1,1+FIND("*",SUBSTITUTE(E1,",","*",B2)),32767))

Затем в конце последняя ячейка будет содержать текстовую строку, которую нужно преобразовать в формулу =AVERAGE() с помощью одного из методов в https://stackoverflow.com/questions/4471884/turn-a-string-formula- в-в-реальный формуле

К сожалению, вы можете столкнуться с лимитом длины ячейки, который составляет 32768 символов.