Forum Discussion
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...
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.
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!
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 🙂
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!
Apologies, there was no negative association intended, it was merely a tongue-in-cheek comment that while some formulas may appear less than transparent the process of construction certainly has merit. The remark was qualified by a {wink} which regrettably i now see was removed by the html parser when posting.
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 SergeiBaklan still exist and embrace both worlds!
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.
PeterBartholomew1 , why don't you ask techcommunity@microsoft.com to help?
