FIFO Inventory Formula Challenge

Silver Contributor

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!

Twifoo_0-1612784589338.png

 

84 Replies

@Twifoo 

I have had the audacity to work on @Sergei Baklan 's version of the solution.  My reason for doing this was that I wanted to see how easy (or difficult) collaborative development might be.  My first finding was that a utility that steps through the local names within a LET function returning their values one by one would be of immense value.  The second conclusion is that working with someone else's LET formula is, indeed, easier than working with traditional formulas (such as we were meant to create for the present challenge).

 

I did make a change to Sergei's formula in that I replaced the matrix multiplication MMULT by a simpler SUM.  To take the work forward I then wrapped the LET with LAMBDA to improve the parameter passing (I detest direct cell references in general and was never going to find one buried in the middle of a multi-line function appealing, despite its being appropriate to the challenge).

 

= LAMBDA(prNm,
  LET(
    type, -(EVEN(TrnType="Sale")-1),
    prod, (ProductName=prNm),
    table, CHOOSE({1,2,3,4},TrnNum,TrnQty*type, TrnPrice, type*prod),
    total, SUM(INDEX(FILTER(table,prod),0,2)),
    two, INDEX(SORT(FILTER(table, prod*type=1),,-1),{1;2},{2,3}),
    price, INDEX(two,,2),
    lastQty, MIN(total, INDEX(two,1,1)),
    QTYs, CHOOSE({1;2}, lastQty, total-lastQty),
  SUM(QTYs*price))
  )(@Product)

I have yet to clarify thoughts on how the solutions to such problems might best be packaged.  One option might be to output the results to a table and create a rich data type with inventory quantities and values as attributes.  Another might be to use recursion to generate values for the five product lines as a single array.

 

I also look forward to @Twifoo 's solution in due course.  It does me no harm to be shown that amazing solutions can be created with traditional techniques.

I'm already accustomed to your inherent aversion of using direct cell references. Even so, I'm impressed at your improvement on the solution of @Sergei Baklan .
As I had previously advocated, traditional formulas are accessible to more users than modern Excel functions. For that reason, my formula challenges reach the widest possible audience, rather than a privileged few.

@Twifoo 

Thank you for the explanation. Still had no time to check in details, will do.

Question to your latest post, related to this case what do you mean under traditional functions? If people have LET() they shall have SORT(), FILTER(), etc. If only LAMBDA() is still exotic, but here it is only cosmetic on the top of LET().

@Sergei Baklan 

I simply adopted the adjective "traditional" mentioned by @Peter Bartholomew .
Perhaps, "traditional functions" would refer to those which are not available to non-Microsoft 365 users. Using LET is analogous to "legal cheating" for allowing the definition of names outside the Name Manager. For the purpose of my formula challenge, I will allow such, not because I allow cheating, but because it is legal.

@TwifooDeleted previous reply, getting used to the set-up on the forum. I've used LET below for readability and in the first cell in the attached, but left other formulas using older functions. On the right track?

 

=LET(
Product,"Courage",
ProdFilter,(ProductName=Product),
PurchFilter,(TrnType="Purchase"),
InventoryOnHand,SUM(CHOOSE(MATCH(TrnType,{"Purchase";"Sale"},0),1,-1)*ProdFilter*TrnQty),
LastPurchasePrice,INDEX(TrnPrice,MAX(TrnNum*PurchFilter*ProdFilter)),
LastPurchaseQty,INDEX(TrnQty,MAX(TrnNum*PurchFilter*ProdFilter)),
LastPurchaseInv,MIN(InventoryOnHand,LastPurchaseQty),
PrevPurchasePrice,INDEX(TrnPrice,LARGE(TrnNum*PurchFilter*ProdFilter,2)),
PrevPurchInv,(InventoryOnHand-LastPurchaseInv),
LastPurchaseInv*LastPurchasePrice+PrevPurchInv*PrevPurchasePrice)

@Twifoowrote Using LET is analogous to "legal cheating" for allowing the definition of names outside the Name Manager. For the purpose of my formula challenge, I will allow such, not because I allow cheating, but because it is legal.

 

That's one way of looking at LET.

 

I was thinking of LET more as a way of using what we often refer to as "helper columns," just that all of those "helper columns" end up being incorporated into (or referenced within) a single formula. In fact, that has given me insight into how to use LET more extensively, whenever tempted toward the "helper column" approach.

@Twifoo 

It was all going so well!  Because the formulae used in the calculation prevented the function being called with the array of 5 products, I set out to use recursion to build the solution array.

This involved a wrapper function FOREACH that builds the recursion stack and XSTACK that extracts results following the return from each level.

image.png

I returned the array of inventory values as required.  Then I tried to SUM the amounts directly.  It turns out that the result is not recognised as an array, despite the fact that it may be output as an array.

 

@Sergei Baklan @Riny_van_Eekelen 

Any ideas?

My analysis of the LET syntax reveals that it defines a name for the value in an argument, then use such name in the main formula. If such value, which has now been defined as a name, won't be reused in the main formula, such definition becomes superfluous!

@Twifoo 

LET provides a level of inbuilt documentation (roughly equivalent to a helper range with adjacent annotation) but the name is a slight penalty in terms of formula length if its formula is used just once.  In terms of its function, it is precisely the same as the nested formula that would result from substituting the formula in place of the name where it used.  I find the sequential form easier to follow than deeply nested formulas.

 

There is a computational advantage, as well as formula length gain, if the named formula is used multiple times as in

= IF(ROW(INDIRECT("A1:A"&1+ROWS(array)))<1+ROWS(array),

   INDEX(array, ROW(INDIRECT("A1:A"&1+ROWS(array))) ),

   scalar) )

which could be used in place of

= IF(k<n, INDEX(array, k ),scalar) )

 

By tradition Excel techniques, the things I had in mind were

1. the direct referencing of cells

2. the use of filled formulas with relative referencing

3. a reluctance to use array formulas

4. the use of deeply nested formulas

 

The built-in functions, with a few notable exceptions provide an area of commonality between traditional working practices and those predicated on dynamic arrays.  Now with the Lambda function Excel is entering the world of being a full-on programming platform.  The way in which one conceives solutions changes completely.  I just hope the users are up to it!

 

@Twifoowrote, responding to my analogizing LET as a welcome variation (improvement) on helper columns,  My analysis of the LET syntax reveals that it defines a name for the value in an argument, then use such name in the main formula. If such value, which has now been defined as a name, won't be reused in the main formula, such definition becomes superfluous! 

 

And I fully agree. LET does in fact define a name for a value, often for the result of an independent calculation.

 

In doing that latter, it operates as an improvement on the less efficient, albeit probably easier-for-the-novice-to-follow use of helper columns. That was my only point; LET is a nice, and once grasped actually entirely intelligible, way to accomplish some remarkable combinations of calculations that hitherto were separated into multiple columns.

To gratify the admirers of LET, I will present my legacy formula, along with its modern version, when I will ultimately divulge my solution. I will thenceforth prove that LET only reduces the length of a legacy formula by substituting intermediate results with names defined within it.

 

If you still remember, and I hope you do, NumToWords, NumToDollars, and NumToPounds are named formulas that I created before the birth of, and were perhaps the inspiration for, the LET and LAMBDA functions in Modern Excel.

@Twifoo 

With that straightforward formula could be like

=SUM(
  INDEX(TrnPrice,
            AGGREGATE(14,6,1/(ProductName=$J20)/(TrnType="Purchase")*TrnNum,{1;2}),
            0)*
  CHOOSE({1;2},
              MIN(
                    INDEX(TrnQty,
                                  AGGREGATE(14,6,1/(ProductName=$J20)/(TrnType="Purchase")*TrnNum,1),
                      0),
                    SUM(TrnQty*(1-EVEN(TrnType="Sale"))*(ProductName=$J20))
              ),
              SUM(TrnQty*(1-EVEN(TrnType="Sale"))*(ProductName=$J20))  -
              MIN(
                     INDEX(TrnQty,
                                  AGGREGATE(14,6,1/(ProductName=$J20)/(TrnType="Purchase")*TrnNum,1),
                     0),
                     SUM(TrnQty*(1-EVEN(TrnType="Sale"))*(ProductName=$J20))
              )
  )
)

I guess it could be more compact with another approach, will try later. But in any case I'm not going to use global names. Oh, forgot, it shall be sumproduct here.

@Twifoo 

One more... was trying to save characters, could be made clearer by using longer names:

=LET(
T,TrnType="Purchase",
P,ProductName=J18,
B,SORTBY(IF({1,0},TrnQty*-P*-1^T,TrnPrice),TrnType,1,TrnNum*P,-1),
S,SUM(INDEX(B,,1)),
MMULT(MIN(S,INDEX(B,1,1))*{1,-1}+{0,1}*S,INDEX(B,{1;2},2))
)

and a slightly shorter one based on the sample file:

=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}))))

I couldn't think of any nice ways to do this in old style formulas, interested to see your formula...

 

@Sergei Baklan 

I added the necessary coercions and had a go at reducing the length of your legacy formula:

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

 Am sure there is room for futher improvement...

@lori_m 

Hi Lori

It shouldn't take you more than a few minutes to write a recursive Python script to reduce any LET function back to its simplest form (grin)!  Whether the resulting formula is 'nice' or not is a matter of taste.  Like beauty, simplicity is in the eye of the beholder.  What others see as simplicity I see as primitive and unstructured, i.e. a classic demonstration of complexity!

 

BTW I reported the behaviour of my recursive formula that produced an array of results for the 5 products to Microsoft as an error.  Oddly, although I could correctly output the array, I could not then count or sum the values it held (the start of the problem was that the 5 values were represented within 1 row?

@lori_m 

Improvement is certainly the biggest room in the world. Your coercion and reduction of the legacy formula constructed by @Sergei Baklan are evocative of your stunning contributions in past formula challenges. Having enjoyed studying the formula solutions therein, my version of such challenges was born, with conceivable application to real business circumstances.

@lori_m , thank you. Yes, most probably it could be improved.

@Peter Bartholomew 

Agree discussions around simplicity / complexity, etc. are pretty pointless given their subjective (and recursive!) nature. I was thinking of 'Nice' in a 'Pythonic' sense and it does seem Excel is heading in the direction of such languages.

 

I don't have access to LAMBDA to test but it should be possible to use recursion within a LET statement by doing something along the lines of this Python code:

Y = lambda f: (lambda x: x(x))(lambda y: f(lambda *args: y(y)(*args)))
fib = lambda f: lambda n: (n if n<2 else f(n-1)+f(n-2))

>>> list(map(Y(fib),range(10)))
[0, 1, 1, 2, 3, 5, 8, 13, 21, 34]

 

@Twifoo 

Thanks, I'm flattered - and I'm sure there are many more tricks out there waiting to be discovered. One strategy in the past I used to remove repeated terms was to make use of the identity for single column arrays X>=0 and Y>=0:

 

MIN(X,Y)=MMULT(ABS(X-{1,-1}*Y),{-1;1}/2)

 

but that doesn't make it any clearer. I'm glad we have LET so don't have to do that sort of thing now!

@lori_m 

I wouldn't say that I have reached the point of being able to read the concise syntax that seems to be favoured in the world of functional analysis with any degree of fluency!  Even in the world of imperative computing I always preferred verbose to concise (Visual Basic to Java).  The Y combinator still leaves me struggling to read what is the function definition and what arguments are being processed.  With conventional math, which is somewhat concise, I instinctively feed in phases such as 'with respect to' but it has yet to happen with Curry's notation.

 

Despite that, I seem to have written a recursive Lambda function that I have applied to @Twifoo 's challenge problem and then reapplied it to a simpler problem (that of calculating a 1D column that represents row totals of a 2D array) without change.

image.png

It might be that FOREACH implements the Y combinator and XSTACK the MAP function but, right now, I lack the clarity of thought to be sure.  For the sake of completeness, my function to sum rows was

= LAMBDA(arr,num,
    SUM(INDEX(array,num,0))
  )

Not totally awe inspiring, but useful.