How to Calculate LIFO with Dynamic Arrays

[ds100]

Hello All,

I have a list of purchases and sales. In a new column, I would like to show the LIFO cost basis for each sales row. 

Using the "Let" function, I started down this path by loading (i) the transactions in reverse order, and (ii) determining the position of the last purchase applied to the current sales cost basis.  However, I got stuck trying to develop a data array formula which properly allocates individual purchases btwn prior sales and the current row sales.  

Thank you in advance.

Hypothetical purchases/sales:

time				cost basis
sequence	P/(S)	Qty	Price	of sales
1	P	100	$10
2	P	200	11
3	P	300	12
4	P	400	13
5	P	500	15
6	S	(1,000)	20
7	P	500	18
8	S	(250)	21
9	P	500	19
10	P	200	17
11	S	(1,000)	19

[#2]

@ds100 I think a possible approach would be to use the sort and filter functions.  For example this will filter for only the Purchases and then sort in reverse order:

=SORT(FILTER(A3:D13,B3:B13="P",""),1,-1)

see attached

[#3]

@ds100 

Good luck with that as a task!

I probably have used functions that you do not have and would still require a lot of work to boil the calculation down to a single formula!

Note: SUMIFS can provide an alternative to SCAN when a running total is required.

[#4]

Thanks @Peter Bartholomew 

I hope Lambda and Scan hit 365 soon

[#5]

Thanks. We think alike.

[#6]

If you are working with a corporate license you would need IT to sign you up for Insider beta for a while. I don't think the 2D grid I laid out is strictly necessary but it helped me work through the problem; FIFO calcs are not my forte!

[#7]

that's good to know. thank you