Forum Discussion

SimoneBraccio's avatar
SimoneBraccio
Copper Contributor
Dec 17, 2024

Fifo days in inventory logic

Hi, I have been trying to build a dynamic array logic to dynamically automate the calculation of days in inventory of some products. I am not an expert in using functions like scan/lambda and I ...
BI & Data Analysis
excel
Formulas and Functions
Kidd_Ip's avatar
Kidd_Ip
MVP
Dec 17, 2024

You may start from this:

 

=LET(
    purchaseDates, A2:A10,  // Range of purchase dates
    quantitiesPurchased, B2:B10,  // Range of quantities purchased
    saleDates, C2:C10,  // Range of sale dates
    quantitiesSold, D2:D10,  // Range of quantities sold
    runningTotal, SCAN(0, quantitiesSold, LAMBDA(a, b, a + b)),
    daysInInventory, LAMBDA(purchaseDate, saleDate, saleDate - purchaseDate),
    result, MAP(purchaseDates, saleDates, daysInInventory),
    result
)

 

Resources