How to Calculate LIFO with Dynamic Arrays

New 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
1P100$10 
2P20011 
3P30012 
4P40013 
5P50015 
6S(1,000)20 
7P50018 
8S(250)21 
9P50019 
10P20017 
11S(1,000)19 

 

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

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

see attached

 

@ds100 

Good luck with that as a task!

image.png

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

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