FIFO Inventory Formula Challenge

[Twifoo]

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 @Peter Bartholomew @Sergei Baklan and @lori_m could again spice our learning experience!

[#2]

@lori_m I've tried a couple of different angles on it, and sweated the characters out a bit, but yours still wins:

@keenadvice attempt 1 now 206 characters

=LET(
A,(TrnType="Purchase")*(ProductName=J18),
B,INDEX(SORT(FILTER(TrnQty,A)),1),
C,SUM(A*TrnQty)-SUM((TrnType="Sale")*(ProductName=J18)*TrnQty),
SUM(INDEX(SORT(A*TrnPrice,,-1),{1,2})*CHOOSE({1,2},B,C-B)))

@keenadvice attempt 2 191 characters

=LET(
A,(ProductName=J18),
B,(TrnType="Purchase")*A,
C,SORT(CHOOSE({1,2},TrnPrice,TrnQty)*B,,-1),
D,INDEX(C,1,2),
INDEX(C,1,1)*D+INDEX(C,2,1)*(SUM(B*TrnQty)-SUM((TrnType="Sale")*A*TrnQty)-D))

@lori_m 172 characters

=LET(
T,TrnType="Purchase",
P,ProductName=J18,
S,SUM(-1^T*-P*TrnQty),
MMULT(MIN(S,LOOKUP(2,1/T/P,TrnQty))*{1,-1}+{0,1}*S,
INDEX(TrnPrice,AGGREGATE(14,6,TrnNum/T/P,{1;2}))))

[#3]

@keenadvice 

That second attempt could be further reduced by using 'IF({1,0}' in place of 'CHOOSE({1,2}' which then beats the first formula I posted by one character. The second formula I posted was basically just using the same formulas @Twifoo used on the sheet but I prefer the SORT method which I think makes things a bit clearer.

[#4]

Credit to @lori_m for making me think about how to shorten what's going on inside the SORT

This version is 182 characters

(which is not the only thing we should be obsessed with!)

=LET(
A,(ProductName=J18),
B,(TrnType="Purchase")*A,
C,SORT(((TrnPrice:TrnQty)*B),2,-1),
D,INDEX(C,1,1),
INDEX(C,1,2)*D+INDEX(C,2,2)*(SUM(B*TrnQty)-SUM((TrnType="Sale")*A*TrnQty)-D))

[#5]

@keenadvice 

Just checked and your formulas match expected results for first few but last two don't match for me.

[#6]

Newer versions than 1907 Excel, calculation is broken. xlcalculationAutomatic will take over 50times longer in new versions (2012 and 2101) than early versions. Its totally screwed up.

[#7]

@lori_m you are right

(I am sure there is a lesson there about testing regimes and actually testing thoroughly!)

This version is now 206 characters:

=LET(
A,(ProductName=J18),
B,(TrnType="Purchase")*A,
C,TrnQty*B,
D,(TrnType="Sale")*A*TrnQty,
E,SORT(((TrnPrice:TrnQty)*B),2,-1),
F,MIN(INDEX(E,1,1),SUM(C-D)),
INDEX(E,1,2)*F+INDEX(E,2,2)*(SUM(C)-SUM(D)-F))

[#8]

@rpbenz  wrote: Newer versions than 1907 Excel, calculation is broken

I know Excel has been around for a while, but had no idea it went back that far. It's older than I am, and I've been retired since 2002.   ;)

[#9]

@mathetes 

Microsoft messed the terms. If former time there were Office 95,...,2010, 2013,... etc and we called these as versions, now it's Office/Microsoft 365 and versions are actually branches (my understanding) within it. Currently from 1908 on one of semi-annual channels to 2013 on beta. Yes, there are such Excel editions as Excel 2019 and recently announced Excel 2021, not sure about versioning in them.

As for the performance - can't comment, no stats and nothing concrete is here. In general after the year 2018 fall performance is only improving. 

[#10]

@keenadvice 

Combining your SORT formula with previous techniques (148 chars):

=LET(A,ProductName=J18,B,TrnType<"Q",C,SORT(TrnPrice:TrnQty*B*A,2,-1),D,
INDEX(C,{1,2},2),MIN(SUM(D*{1,-1})*INDEX(C,1,1)*{0,1}-D*SUM(TrnQty*-1^B*A)))

Edit: looking again although these last two formulas return expected results additional assumptions are being made relating to sort order that may not hold in general. Awaiting @Twifoo's solution...

[#11]

@lori_m 

Maybe this is a better version of the SORT

That helps iron out the potential bugs you identified?

=SORTBY(TrnQty:TrnPrice*(TrnType="Purchase")*(ProductName=J18),
TrnNum*(TrnType="Purchase")*(ProductName=J18),
-1)

New formula becomes:

=LET(A,ProductName=J18,B,TrnType<"Q",C,SORTBY(TrnPrice:TrnQty*B*A,TrnNum*B*A,-1),D,
INDEX(C,{1,2},2),MIN(SUM(D*{1,-1})*INDEX(C,1,1)*{0,1}-D*SUM(TrnQty*-1^B*A)))

[#12]

Here is the link to my detailed explanation of my two solutions:
https://roberthontoriagascon.medium.com/these-fifo-inventory-formulas-will-challenge-your-array-mani...

[#13]

If you imagine that Chess Moves could be translated to Excel Formulas, I earlier posted my version of my imagination here:
https://techcommunity.microsoft.com/t5/excel/excel-chessgames-viewer/m-p/765672

[#14]

@Twifoo Nice technique and explanation!

Bearing in mind ABS(x) = x^2^0.5, our methods are not too dissimilar. For 'Courage' the second formula I posted evaluates to:

=SUM({76;65}*{141,-40;40,40})

where for the sake of brevity an extra MMULT was omitted that would have resulted in:

=SUM({76;65}*{101;80})

Aside: It also occurred to me to use MDETERM({76,-80;65,101}) which returns the same result of 12876. Maybe not so useful in this case but could save looking up prices and quantities individually for calculating total cost =65*760+76*101.

[#15]

@Twifoo 

Robert

I appreciated your description of the problem and your analysis of solution requirements.  I also learn from the detailed techniques that have been deployed to make a formula more concise, though that has never been an objective of mine.  My shortcoming is that I find 'concise' notations, such a regular expressions (RegEx), fiercely difficult to read.

I wish I could think up competition rules for 'the clearest top-down solution strategy' in which it is the number of overall operations that count.  In my solution, posted on 13 Feb, I used Charles Williams FastExcel functions to solve the problem as an accumulate/minimise/difference sequence.  That itself is simply a solution strategy to the generic problem of 'allocate an amount to consecutive bands' (e.g. the allocation of taxable income across tax bands).  Your challenge is the same problem and could also be solved without placing limits on the purchasing policy that ensure only the final two tranches are present within the residual stock.  Having got an ACCUMULATE function, I now need an ALLOCATE function.

Once the LAMBDA functions are released, one could imagine a challenge of:

Solve @Twifoo 's year2 problem for all 5 products within a single recursion formula.

To return to the topic, I think the ingenuity required to create the problem is even more impressive than its solution; congratulations.

[#16]

Postscript. I am not sure that I will be posting on this forum again. I have booted up an old computer that still recognises my user credentials but, on my current computer, I cannot get past the Community Registration page that insists I set up a new profile. All that because I logged on to a client SharePoint with credentials they provided!

[#17]

@Peter Bartholomew , why don't you ask techcommunity@microsoft.com to help?

[#18]

@Peter Bartholomew 

Hoping you manage to resolve those issues, I have gleaned much from examining the structured approach you take to problems. One take away from the present challenge is the advantage of the modern code-style LET / LAMBDA solutions over the old "mega formula" approach which I'm only too happy to have left behind now. As with many things, it is the journey not the destination that counts - especially when the resulting formulas start to bear resemblance to entries for a code obfuscation contest :)

[#19]

@lori_m 

With your guidance using a LinkedIn message, I have at least found my way back here using Google Chrome.  Microsoft Edge still steers me to the new Registration page.

I think the present state of affairs with Excel is "interesting".  Spreadsheets epitomise 'end user computing' and, as such, I cannot see the majority of users making the move to exploit the new features.  At the same time there are probably others, like me, who will never use 'old style spreadsheeting' again; I would sooner consign the software to the trash can.  The downside there is that I have to refuse to take on any non-365 clients.  It is probably important people like @Sergei Baklan still exist and embrace both worlds!

[#20]

@Peter Bartholomew 

Edge - I have few profiles with it, and if I try to open MTC in one with different account, it also forwards me on registration page. Some ago I have issues with ADFS for the main profile, also was not able to login and used Vivaldi for a while. Fortunately admins solved an issue. Don't know what was the reason, just share an experience.

Excel - transition period for any product is not fast, especially for such one as Excel which has millions of users. Some still use Excel 2000 and/or have no clue about tables and like 10-years old things. However, with Excel 2021 introduction I hope dynamic arrays functionality will be used much wider. As for the lambdas - this technology is at very beginning, right now it's only to play with it. Even in it production stage I don't think it will be used by everyone, like VBA now. Perhaps same about Office Scripts. We have what we have, but I think that Excel provides the niche for everyone.

[#21]

Were it not for your perhaps inadvertent qualification of my formula challenge as resembling a code obfuscation contest, I would have been completely delighted by your thesis that it is the journey that counts, not the destination, To eliminate any iota of ambiguity, let me clarify that I like legacy formulas, for they provide the logical path, but I love modern formulas, for they provide the shortest path, towards my ultimate solution!