Forum Discussion
Twifoo
Feb 08, 2021Silver Contributor
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...
Riny_van_Eekelen
Feb 09, 2021Platinum Contributor
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.
- TraderbearSep 12, 2023Copper Contributor
I have applied your Let algorithm to a FIFO calculation for option trading. I have been able to get it to work--partially. To use it this way, I had to allow for negative inventory (equity) values. There are cases where it gives the wrong answer. If you are interested in helping me find the problem, I can send you a spreadsheet that shows the problem.
Thanks,
Paul Accampo
- TwifooFeb 10, 2021Silver Contributor
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.
- mathetesFeb 10, 2021Silver Contributor
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?
- mathetesFeb 10, 2021Silver Contributor
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))
- mathetesFeb 09, 2021Silver Contributor
Well done Riny_van_Eekelen !
I''m still working on it (in my head mostly)...later on today I will be home where I have access to two more reasonable screens, rather than just a small MacBook Air.... that should help.
It would be nice if one of us--it won't be me--were to come up with a LAMBDA based solution.