Forum Discussion

Twifoo's avatar
Twifoo
Silver Contributor
Feb 08, 2021

FIFO Inventory Formula Challenge

To revel my second anniversary as a member of this EXCELlent Community, I hereby proffer this challenge to season your array manipulation skills. Perhaps, keenadvice mathetes Riny_van_Eekelen PeterBa...
Formulas and Functions
Twifoo's avatar
Twifoo
Silver Contributor
Feb 09, 2021

Riny_van_Eekelen 

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. 

mathetes's avatar
mathetes
Gold Contributor
Feb 09, 2021

Twifoo 

 

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?