Forum Discussion
FIFO Inventory Formula Challenge
Twifoo Don't really now what "Array manipulation" is and I'm probably violating all the rules of the challenge. Sorry about that. I used your challenge to play around with LET, in stead.
I would define array manipulation as the skillful control of formula results. Nonetheless, the challenge is to derive the correct results, regardless of the functions used thereby.
OK, if permitted, I'm going to withdraw my resignation from the match. I stuck it out after all (one thing that helped a lot: I learned how to do line breaks in the Formula Bar, which makes it a lot more readable, as all of you know). Here's the formula:
=LET(
PQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Purchase")),
SQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Sale")),
NQt,SUM(PQt)-SUM(SQt),
LPuR,COUNT(PQt),
ARQtPr,FILTER(TrnQty:TrnPrice,(ProductName=J18)*(TrnType="Purchase")),
LPuQ,MIN(NQt,INDEX(ARQtPr,LPuR,1)),
LPuP,MIN(NQt,INDEX(ARQtPr,LPuR,2)),
SLPuQ,MIN(NQt-LPuQ,INDEX(ARQtPr,LPuR-1,1)),
SLPuP,MIN(NQt,INDEX(ARQtPr,LPuR-1,2)),(LPuQ*LPuP)+(SLPuQ*SLPuP))
I could not make SUMPRODUCT work with the last four "name values" -- any idea why?
Interesting! I noticed an error in the solution I posted earlier. It didn't adversely affect the result (although it might have under some other conditions), but it was wasted characters in that they added no value.
Here's the corrected (and more streamlined) version.
=LET(
PQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Purchase")),
SQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Sale")),
NQt,SUM(PQt)-SUM(SQt),
LPuR,COUNT(PQt),
ARQtPr,FILTER(TrnQty:TrnPrice,(ProductName=J18)*(TrnType="Purchase")),
LPuQ,MIN(NQt,INDEX(ARQtPr,LPuR,1)),
LPuP,INDEX(ARQtPr,LPuR,2),
SLPuQ,MIN(NQt-LPuQ,INDEX(ARQtPr,LPuR-1,1)),
SLPuP,INDEX(ARQtPr,LPuR-1,2),(LPuQ*LPuP)+(SLPuQ*SLPuP))
