How to Calculate LIFO with Dynamic Arrays

Copper Contributor

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
sequenceP/(S)QtyPriceof sales


7 Replies

@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:


see attached



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.

Thanks @Peter Bartholomew 

I hope Lambda and Scan hit 365 soon :crossed_fingers:

Thanks. We think alike.
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!
that's good to know. thank you
This is amazing. I've been looking for this kind of solution for months. How can I use this for multiple products. Regads,