Forum Discussion
FIFO Inventory Formula Challenge
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.
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.
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.
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!
