Forum Discussion

Twifoo's avatar
Twifoo
Silver Contributor
Feb 08, 2021

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...
Formulas and Functions
lori_m's avatar
lori_m
Iron Contributor
Feb 21, 2021

keenadvice 

Combining your SORT formula with previous techniques (148 chars):

 

=LET(A,ProductName=J18,B,TrnType<"Q",C,SORT(TrnPrice:TrnQty*B*A,2,-1),D,
INDEX(C,{1,2},2),MIN(SUM(D*{1,-1})*INDEX(C,1,1)*{0,1}-D*SUM(TrnQty*-1^B*A)))

 

Edit: looking again although these last two formulas return expected results additional assumptions are being made relating to sort order that may not hold in general. Awaiting Twifoo's solution...

  • lori_m's avatar
    lori_m
    Iron Contributor
    Feb 27, 2021

    Twifoo Nice technique and explanation!

    Bearing in mind ABS(x) = x^2^0.5, our methods are not too dissimilar. For 'Courage' the second formula I posted evaluates to:

    =SUM({76;65}*{141,-40;40,40})

    where for the sake of brevity an extra MMULT was omitted that would have resulted in:

    =SUM({76;65}*{101;80})

    Aside: It also occurred to me to use MDETERM({76,-80;65,101}) which returns the same result of 12876. Maybe not so useful in this case but could save looking up prices and quantities individually for calculating total cost =65*760+76*101.

Resources