How to Calculate LIFO with Dynamic Arrays

Question

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.&nbsp;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.&nbsp; However, I got stuck trying to develop a data array formula which properly allocates individual purchases btwn prior sales and the current row sales.&nbsp;&nbsp;Thank you in advance.Hypothetical purchases/sales:time&nbsp;&nbsp;&nbsp;cost basissequenceP/(S)QtyPriceof sales1P100$10&nbsp;2P20011&nbsp;3P30012&nbsp;4P40013&nbsp;5P50015&nbsp;6S(1,000)20&nbsp;7P50018&nbsp;8S(250)21&nbsp;9P50019&nbsp;10P20017&nbsp;11S(1,000)19&nbsp;&nbsp;

peterbartholomew1 · Answer

ds100&nbsp;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!&nbsp;Note: SUMIFS can provide an alternative to SCAN when a running total is required.

mtarler · Answer

ds100&nbsp;I think a possible approach would be to use the sort and filter functions.&nbsp; 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&nbsp;

ds100 · Answer

Thanks&nbsp;PeterBartholomew1&nbsp;I hope Lambda and Scan hit 365 soon 🤞

ds100 · Answer

Thanks. We think alike.

peterbartholomew1 · Answer

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!

Forum Discussion

How to Calculate LIFO with Dynamic Arrays

7 Replies

Resources

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