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]

I forgot to say, the function call was

= FOREACH(ROWS(array), SUM1ROW, array)

The first parameter would appear to be superfluous but the variable it initialises served as a counter in order to terminate the recursion.

BTW the formula you wrote for Currying the flow within the accumulation works (I assumed you had been in a position to test it).  Whether it serves the intended purpose of reducing memory demands or parameter count, I have yet to determine.

[#3]

@Sergei Baklan 

Managed to reduce that legacy formula to 256 chars using identity above and LOOKUP

=SUM(INDEX(TrnPrice,
AGGREGATE(14,6,1/(ProductName=J18)/(TrnType="Purchase")*TrnNum,IF(1,{1;2})),0)*
MMULT({1,0;-1,1},ABS(LOOKUP(2,1/(ProductName=J18)/(TrnType="Purchase"),TrnQty)*{1;0}+
{1,-1;2,0}*SUM(TrnQty*-1^(TrnType="Sale")*(ProductName=J18)))*{1,-1}/2))

(interesting as a challenge though hardly very readable!)

[#4]

@lori_m 

That was a significant 20% reduction from your previous formula of 320 characters! You never fail to amaze me.

[#5]

@Twifoo 

I would like to thank you for the challenge.  It is a demanding problem in its own right even before the 'minimum key presses' aspect of the challenge.

For me, it has provided an opportunity to improve my understanding of Lambda functions, including setting up recursion to return an array of results.  It has also allowed me to identify what I believe is an error in the beta implementation, which I have reported.

To return to the theme of the challenge for a moment, a few more characters can be sweated out of Lori's formula by modifying the identification of transaction type.

=SUM(INDEX(TrnPrice,AGGREGATE(14,6,1/(ProductName=J18)/(TrnType<"Q")*TrnNum,IF(1,{1;2})),0)*MMULT({1,0;-1,1},ABS(LOOKUP(2,1/(ProductName=J18)/(TrnType<"Q"),TrnQty)*{1;0}+{1,-1;2,0}*SUM(TrnQty*-1^(TrnType>"Q")*(ProductName=J18)))*{1,-1}/2))

I also remove line-feeds to reduce the formula length to 239.  My apologies for making such mundane changes once all the hard work has been done.

[#6]

I did that in my solution, too.

[#7]

@Twifoo 

As probably (undoubtedly) the least proficient person privileged to be included in this chaiN--least proficient in the Functions hidden away in the dark corridors of Excel manuals--I would like to go back and underscore the comment made by @lori_m in her last submission.

Readability.

Intelligibility.

That needs to be a factor in all of this too, although I will certainly grant that it would be hard to measure. I definitely have learned from the challenge--and can accept readily that learning, stretching, growing may be the main goal. For me learning how to use LET in a more ambitious challenge was a transformative experience: I've gone back to one of my most important spreadsheets and re-written into a single LET formula a task that formerly involved two or three steps, an elaborate two-dimensional matrix, and a final multi-layered OFFSET. 

So maybe my concern is more that when we're resolving some of the questions that get posed in this forum we take readability into account, taking a few moments to give more of an explanation of how and why the solution works. I know for myself that it's often the case that I think the "why a solution works" part is obvious. In some ways, for sure, it's unfortunate that Excel doesn't provide for side-by-side documentation within the many more powerful functions. It looks like it may in LAMBDA -- I have yet to do other than read some of your fantastic implementations of that great new feature. If indeed side-by-side documentation is included, that is an important feature.

I know that clarity is a big part of the aversion expressed by @Peter Bartholomew for direct cell references and fully concur; I find myself, for that reason, often pointing out the dangers of hard-coding variables into formulas, given that variables often live up to their name.

Here's a suggestion for you, @Twifoo : in your third year challenge consider including a criterion--along with successful results and shortest length--for the clearest explanation for the somewhat proficient Excel wanna-be.

In closing, though, let me express gratitude for this opportunity from this somewhat proficient Excel wanna-be. Thanks for the 2021 FIFO Inventory Challenge!

[#8]

@mathetes 

I excluded "discernible substance" from the rules of the challenge because I didn't anticipate @lori_m could devise another stunning formula that, though she indirectly admitted as lacking such substance, nonetheless returns the required result. Somehow, we may learn new techniques therefrom. 

Had I included the foregoing, any formula solution must return the required result as the sum of: 

1. The lower quantity between the physical count and the last purchase, multiplied by the price of the last purchase; and 

2. Any excess quantity of the physical count over the last purchase, multiplied by the price of the second to last purchase. 

I emphatically deem @lori_m as the doyenne of shortest formula challenges. As such, I evaded "shortest" in the description hereof.  Inevitably, the ideal solution must be the shortest formula with discernible substance. Stated differently, such formula must be like a miniskirt, which is long enough to cover the essentials, but short enough to invite attention!

[#9]

@Twifoo  @mathetes 

Good points. 'Pythonic' is the description for recommended style in Python; unfortunately there doesn't seem to be such a term for Excel though perhaps that is because there is less consensus given the wide variety of user experience.

Ps. Also just to clarify preferring a degree of anonymity, 'Lori' is a moniker that should probably be spelled 'Laurie' to indicate gender.

Edit: i realise that didn't clarify gender! Peter has confirmed below :)

[#10]

@Twifoo 

Lori used to be a prolific contributor on Chandoo and there too his shortened name caused confusion with most of the Ninjas assuming it applied to a female.  It was only through LinkedIn that the matter was resolved for me.

[#11]

@Twifoo 

When dynamic arrays came out in 2018, I complained because I couldn't perform accumulations (corkscrews in finance) because that required array breakup, nor process 2D arrays row-by-row (requiring operations on arrays of arrays). That was somewhat ungrateful on my part because the concept of dynamic arrays was already a massive step forwards.  What I wanted to do was to create entire models that behaved dynamically and even one or two formulas that relied upon filled ranges or CSE arrays prevented that.

  @Charles Williams was kind enough to implement two functions (ACCUMULATE and DIFF) in FastExcel which help address the present problem.

Why FastExcel V4 - Decision Models

Characters = 128

Disqualified for the use of improper functions.

[#12]

[#13]

@lori_m 

I grasped no clarity in ascertaining your gender. Could you be explicit to eliminate any ambiguity?

[#14]

@Twifoo 

Lorimer is male.  He appears to have almost encyclopaedic knowledge of the backwaters of Excel (as does Sergei) but has recently been helping me take my first faltering steps into the world of functional programming, introducing me to concepts such as Currying.  

Life can be confusing can't it (big grin)?

[#15]

@Peter Bartholomew 

Thanks for clarifying, I am known universally as Lori which unfortunately is ambiguous. High praise but I have moved away from Excel to other languages (mainly python) in recent years and have large gaps in knowledge in many data related areas, power query, sql, office scripts to name a few. Sergei will have better all round knowledge for sure.

[#16]

@lori_m , from all your great posts/comments I've seen my favorite one is still

=COUNTIF(A1:A3,CHAR(173)&A1)

That's far away from other recent discussions, just simple and genius. But latest is never without former.

[#17]

@Twifoo 

Fully accepting that I am too late to the party here...

...(and desperately trying not to look too hard at others' solutions in an attempt to see what I come up with that's either different or congruent)...

...how does this solution (at 231 characters) do @Twifoo ?

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

[#18]

@lori_m and @Twifoo 

I think maybe these perhaps are the 'winning' solutions here???

It's hard to see how they could be shorter...

Well done @lori_m 

[#19]

@keenadvice 

You'll see my formula soon!

[#20]

@keenadvice 

That's a nice approach and I think it could be made as short if not shorter than mine by using single character names and separate criteria for TrnType and ProductName.

@Sergei Baklan 
Thanks for the kind words, I have certainly learned much from from reading through your posts particularly on the power query side. The CHAR(173) trick for distinguishing numeric strings in text criteria was stumbled upon quite by accident - it arose from being confused about some code that was sorting HTML data and it turned out there were these hidden hyphen characters in the string which were ignored in some situations but not others.

@Peter Bartholomew 
Going back to your earlier point, for writing more efficient code that doesn't create extra stack space, I can't test in Excel but was able to adapt the "tail recursion" tutorials to work with lists as parameters. and I believe could be a promising approach.

[#21]

@keenadvice 

Could someone explain me what is the benefit of using in LET() as short names as possible ? IMHO, that only adds more headache both for debugging and maintenance stage.