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 PeterBartholomew1 SergeiBaklan and lori_m could again spice our learning experience!

Here is the link to my detailed explanation of my two solutions:https://roberthontoriagascon.medium.com/these-fifo-inventory-formulas-will-challenge-your-array-manipulation-skill-129a8f680ad?source=friends_link&sk=915b52e8696e1cf06fad1baff7563a21

Twifoo Don't really now what "Array manipulation" is and I'm probably violating all the rules of the challenge. Sorry about that. I used your challenge to play around with LET, in stead.

Twifoo How do you generate these exercises? Amazing! Here I again didn't understand the logic behind (From Last Purchase Qty), just imitate your formula. First attempt =LET( type, -(EVEN(TrnType="Sale")-1), prod, (ProductName=J18), 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), MMULT(QTYs,price) ) If logic will be more clear perhaps will do another attempt in few days.

Twifoo Robert, I was going to apologise for using your challenges to explore the developing product that is Excel and learning new techniques rather than addressing the terms of the challenge head on. It appears that I may not be alone in this!I did even consider using recursion to keep a running total of stock value transaction by transaction but came to the conclusion that it would be too heavy in terms of its computation and would represent overkill. I also wondered whether there is opportunity for a greater level of nested Lambda functions to expose the overarching logic to a greater extent but getting my brain to shift gears is not coming easily! I had a look at some theory but the function representing recursion λ(λx.x x) (λx.x x)does not come easily to me.

Twifoo Well, guys, if this were a chess match (which, metaphorically, it is) I would tip my king on its side, and reach out to shake your hands. I've truly had fun learning how to nest FILTER within INDEX and COUNT (among others), have formulas that will get each of the salient numbers here, and could write a LET to define all the re-used array formulations, but my head is starting to feel as it it might explode just from contemplating putting them all into a single formula, as Robert has stipulated, to say nothing of actually doing it. So it's been a learning experience for sure, and I look forward to applying my learnings. I'll keep looking over your shoulders in months ahead. I AM going to spend some time studying each of your solutions.

FIFO Inventory Formula Challenge | Microsoft Community Hub

85 Replies

mathetes
Silver Contributor
Feb 08, 2021
Twifoo

Oh, my! It's an honor to be included among such august company, an honor I fear I don't deserve at all. I'm here to learn, and occasionally offer some more basic suggestions.

But, challenged, I'll give it a try later today.
- Twifoo
  Silver Contributor
  Feb 08, 2021
  mathetes An honor, yes; but undeserving, no! With your acceptance, I anticipate your valuable insights that will certainly augment our common quest for knowledge.
  - mathetes
    Silver Contributor
    Feb 09, 2021
    Twifoo
    
    Well, guys, if this were a chess match (which, metaphorically, it is) I would tip my king on its side, and reach out to shake your hands.
    
    I've truly had fun learning how to nest FILTER within INDEX and COUNT (among others), have formulas that will get each of the salient numbers here, and could write a LET to define all the re-used array formulations, but my head is starting to feel as it it might explode just from contemplating putting them all into a single formula, as Robert has stipulated, to say nothing of actually doing it.
    
    So it's been a learning experience for sure, and I look forward to applying my learnings. I'll keep looking over your shoulders in months ahead. I AM going to spend some time studying each of your solutions.

Forum Discussion

FIFO Inventory Formula Challenge

85 Replies

Resources