@mandyturcotte
That is a surprise to me. It would not have been any surprise had you not had access to Excel 365, most users do not realise just how profound the changes since 2018 have been. My belief is that even with a semi-annual license you should have had access to the Lambda helper functions for a while now. Do you control the update cycle, or do you need to go to corporate IT?
@Sergei Baklan Can you advise?
Just in case you can gain access to SCAN and MAP, I have tried to repackage the solution as a Lambda function to facilitate reuse, hopefully for you, but otherwise for anyone else with FIFO based challenges.
Sorksheet formula
= CostFIFOλ(quantitySold, purchaseQuantity, purchasePrice)
CostFIFOλ
= LAMBDA(outputQuantity,inputQuantity,inputCost,
LET(
Addλ, LAMBDA(x, y, x + y),
cumInput, SCAN(0, inputQuantity, Addλ),
cumOutput, SCAN(0, outputQuantity, Addλ),
MAP(cumOutput-outputQuantity, cumOutput,
LAMBDA(u₀,u₁,
SUM(
MAP(cumInput-inputQuantity, cumInput, inputCost,
LAMBDA(v₀,v₁,w,
MAX(MIN(v₁,u₁) - MAX(v₀,u₀), 0) * w
)
)
) / (u₁-u₀)
)
)
)
);
