New Contributor

How to Calculate LIFO with Dynamic Arrays

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.

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

6 Replies

Re: How to Calculate LIFO with Dynamic Arrays

@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

Re: How to Calculate LIFO with Dynamic Arrays

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.

Re: How to Calculate LIFO with Dynamic Arrays

Thanks @Peter Bartholomew

I hope Lambda and Scan hit 365 soon

Re: How to Calculate LIFO with Dynamic Arrays

Thanks. We think alike.

Re: How to Calculate LIFO with Dynamic Arrays

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!

Re: How to Calculate LIFO with Dynamic Arrays

that's good to know. thank you