FIFO Inventory Formula Challenge

%3CLINGO-SUB%20id%3D%22lingo-sub-2117589%22%20slang%3D%22en-US%22%3EFIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117589%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20revel%20my%20%3CSTRONG%3Esecond%20anniversary%3C%2FSTRONG%3E%20as%20a%20member%20of%20this%20EXCELlent%20Community%2C%20I%20hereby%20proffer%20this%20challenge%20to%20season%20your%20%3CEM%3Earray%20manipulation%20skills%3C%2FEM%3E.%20Perhaps%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F898063%22%20target%3D%22_blank%22%3E%40keenadvice%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Band%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3Bcould%20again%20spice%20our%20learning%20experience!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Twifoo_0-1612784589338.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253216i2194138A25204470%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Twifoo_0-1612784589338.png%22%20alt%3D%22Twifoo_0-1612784589338.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2117589%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121846%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121846%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20I'm%20flattered%20-%20and%20I'm%20sure%20there%20are%20many%20more%20tricks%20out%20there%20waiting%20to%20be%20discovered.%20One%20strategy%20in%20the%20past%20I%20used%20to%20remove%20repeated%20terms%20was%20to%20make%20use%20of%20the%20identity%20for%20single%20column%20arrays%20X%26gt%3B%3D0%20and%20Y%26gt%3B%3D0%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EMIN(X%2CY)%3DMMULT(ABS(X-%7B1%2C-1%7D*Y)%2C%7B-1%3B1%7D%2F2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20that%20doesn't%20make%20it%20any%20clearer.%20I'm%20glad%20we%20have%20LET%20so%20don't%20have%20to%20do%20that%20sort%20of%20thing%20now!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121787%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgree%20discussions%20around%20simplicity%20%2F%20complexity%2C%20etc.%20are%20pretty%20pointless%20given%20their%20subjective%20(and%20recursive!)%20nature.%20I%20was%20thinking%20of%20'Nice'%20in%20a%20'Pythonic'%20sense%20and%20it%20does%20seem%20Excel%20is%20heading%20in%20the%20direction%20of%20such%20languages.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20have%20access%20to%20LAMBDA%20to%20test%20but%20it%20should%20be%20possible%20to%20use%20recursion%20within%20a%20LET%20statement%20by%20doing%20something%20along%20the%20lines%20of%20this%20Python%20code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3EY%20%3D%20lambda%20f%3A%20(lambda%20x%3A%20x(x))(lambda%20y%3A%20f(lambda%20*args%3A%20y(y)(*args)))%0Afib%20%3D%20lambda%20f%3A%20lambda%20n%3A%20(n%20if%20n%26lt%3B2%20else%20f(n-1)%2Bf(n-2))%0A%0A%26gt%3B%26gt%3B%26gt%3B%20list(map(Y(fib)%2Crange(10)))%0A%5B0%2C%201%2C%201%2C%202%2C%203%2C%205%2C%208%2C%2013%2C%2021%2C%2034%5D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121610%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121610%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%2C%20thank%20you.%20Yes%2C%20most%20probably%20it%20could%20be%20improved.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121608%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121608%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EImprovement%20is%20certainly%20the%20biggest%20room%20in%20the%20world.%20Your%20coercion%20and%20reduction%20of%20the%20legacy%20formula%20constructed%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bare%20evocative%20of%20your%20stunning%20contributions%20in%20past%20formula%20challenges.%20Having%20enjoyed%20studying%20the%20formula%20solutions%20therein%2C%20my%20version%20of%20such%20challenges%20was%20born%2C%20with%20conceivable%20application%20to%20real%20business%20circumstances.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121539%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121539%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Lori%3C%2FP%3E%3CP%3EIt%20shouldn't%20take%20you%20more%20than%20a%20few%20minutes%20to%20write%20a%20recursive%20Python%20script%20to%20reduce%20any%20LET%20function%20back%20to%20its%20simplest%20form%20(grin)!%26nbsp%3B%20Whether%20the%20resulting%20formula%20is%20'nice'%20or%20not%20is%20a%20matter%20of%20taste.%26nbsp%3B%20Like%20beauty%2C%20simplicity%20is%20in%20the%20eye%20of%20the%20beholder.%26nbsp%3B%20What%20others%20see%20as%20simplicity%20I%20see%20as%20primitive%20and%20unstructured%2C%20%3CEM%3Ei.e.%3C%2FEM%3E%20a%20classic%20demonstration%20of%20complexity!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBTW%20I%20reported%20the%20behaviour%20of%20my%20recursive%20formula%20that%20produced%20an%20array%20of%20results%20for%20the%205%20products%20to%20Microsoft%20as%20an%20error.%26nbsp%3B%20Oddly%2C%20although%20I%20could%20correctly%20output%20the%20array%2C%20I%20could%20not%20then%20count%20or%20sum%20the%20values%20it%20held%20(the%20start%20of%20the%20problem%20was%20that%20the%205%20values%20were%20represented%20within%201%20row%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121378%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20added%20the%20necessary%20coercions%20and%20had%20a%20go%20at%20reducing%20the%20length%20of%20your%20legacy%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(%0A%20%20INDEX(TrnPrice%2C%0A%20%20%20%20%20%20%20%20%20%20%20AGGREGATE(14%2C6%2C1%2F(ProductName%3DJ18)%2F(TrnType%3D%22Purchase%22)*TrnNum%2CIF(1%2C%7B1%3B2%7D))%2C0)%2C%0A%20%20%7B1%3B-1%7D*MIN(INDEX(TrnQty%2C%0A%20%20%20%20%20%20%20%20%20%20%20AGGREGATE(14%2C6%2C1%2F(ProductName%3DJ18)%2F(TrnType%3D%22Purchase%22)*TrnNum%2C1)%2C0)%2C%0A%20%20%20%20%20%20%20%20%20%20%20SUMPRODUCT(TrnQty*-1%5E(TrnType%3D%22Sale%22)*(ProductName%3DJ18)))%2B%0A%20%20%20%7B0%3B1%7D*SUMPRODUCT(TrnQty*-1%5E(TrnType%3D%22Sale%22)*(ProductName%3DJ18))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BAm%20sure%20there%20is%20room%20for%20futher%20improvement...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121157%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20more...%20was%20trying%20to%20save%20characters%2C%20could%20be%20made%20clearer%20by%20using%20longer%20names%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0AT%2CTrnType%3D%22Purchase%22%2C%0AP%2CProductName%3DJ18%2C%0AB%2CSORTBY(IF(%7B1%2C0%7D%2CTrnQty*-P*-1%5ET%2CTrnPrice)%2CTrnType%2C1%2CTrnNum*P%2C-1)%2C%0AS%2CSUM(INDEX(B%2C%2C1))%2C%0AMMULT(MIN(S%2CINDEX(B%2C1%2C1))*%7B1%2C-1%7D%2B%7B0%2C1%7D*S%2CINDEX(B%2C%7B1%3B2%7D%2C2))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20a%20slightly%20shorter%20one%20based%20on%20the%20sample%20file%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0AT%2CTrnType%3D%22Purchase%22%2C%0AP%2CProductName%3DJ18%2C%0AS%2CSUM(-1%5ET*-P*TrnQty)%2C%0AMMULT(MIN(S%2CLOOKUP(2%2C1%2FT%2FP%2CTrnQty))*%7B1%2C-1%7D%2B%7B0%2C1%7D*S%2C%0AINDEX(TrnPrice%2CAGGREGATE(14%2C6%2CTrnNum%2FT%2FP%2C%7B1%3B2%7D))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20couldn't%20think%20of%20any%20nice%20ways%20to%20do%20this%20in%20old%20style%20formulas%2C%20interested%20to%20see%20your%20formula...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120886%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120886%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20that%20straightforward%20formula%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(%0A%20%20INDEX(TrnPrice%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20AGGREGATE(14%2C6%2C1%2F(ProductName%3D%24J20)%2F(TrnType%3D%22Purchase%22)*TrnNum%2C%7B1%3B2%7D)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%200)*%0A%20%20CHOOSE(%7B1%3B2%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20MIN(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INDEX(TrnQty%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AGGREGATE(14%2C6%2C1%2F(ProductName%3D%24J20)%2F(TrnType%3D%22Purchase%22)*TrnNum%2C1)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%200)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SUM(TrnQty*(1-EVEN(TrnType%3D%22Sale%22))*(ProductName%3D%24J20))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20SUM(TrnQty*(1-EVEN(TrnType%3D%22Sale%22))*(ProductName%3D%24J20))%20%20-%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20MIN(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INDEX(TrnQty%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AGGREGATE(14%2C6%2C1%2F(ProductName%3D%24J20)%2F(TrnType%3D%22Purchase%22)*TrnNum%2C1)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%200)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SUM(TrnQty*(1-EVEN(TrnType%3D%22Sale%22))*(ProductName%3D%24J20))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI%20guess%20it%20could%20be%20more%20compact%20with%20another%20approach%2C%20will%20try%20later.%20But%20in%20any%20case%20I'm%20not%20going%20to%20use%20global%20names.%20Oh%2C%20forgot%2C%20it%20shall%20be%20sumproduct%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120793%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120793%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20gratify%20the%20admirers%20of%20LET%2C%20I%20will%20present%20my%20legacy%20formula%2C%20along%20with%20its%20modern%20version%2C%20when%20I%20will%20ultimately%20divulge%20my%20solution.%20I%20will%20thenceforth%20prove%20that%20LET%20only%20reduces%20the%20length%20of%20a%20legacy%20formula%20by%20substituting%20intermediate%20results%20with%20names%20defined%20within%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20still%20remember%2C%20and%20I%20hope%20you%20do%2C%20NumToWords%2C%20NumToDollars%2C%20and%20NumToPounds%20are%20named%20formulas%20that%20I%20created%20before%20the%20birth%20of%2C%20and%20were%20perhaps%20the%20inspiration%20for%2C%20the%20LET%20and%20LAMBDA%20functions%20in%20Modern%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120756%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120756%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3Ewrote%2C%20responding%20to%20my%20analogizing%20LET%20as%20a%20welcome%20variation%20(improvement)%20on%20helper%20columns%2C%26nbsp%3B%20%3CEM%3EMy%20analysis%20of%20the%20LET%20syntax%20reveals%20that%20it%20defines%20a%20name%20for%20the%20value%20in%20an%20argument%2C%20then%20use%20such%20name%20in%20the%20main%20formula.%20If%20such%20value%2C%20which%20has%20now%20been%20defined%20as%20a%20name%2C%20won't%20be%20reused%20in%20the%20main%20formula%2C%20such%20definition%20becomes%20superfluous!%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3EAnd%20I%20fully%20agree.%3C%2FSTRONG%3E%20LET%20does%20in%20fact%20define%20a%20name%20for%20a%20value%2C%20often%20for%20the%20result%20of%20an%20independent%20calculation.%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20doing%20that%20latter%2C%20it%20operates%20as%20an%20improvement%20on%20the%20less%20efficient%2C%20albeit%20probably%20easier-for-the-novice-to-follow%20use%20of%20helper%20columns.%20That%20was%20my%20only%20point%3B%20LET%20is%20a%20nice%2C%20and%20once%20grasped%20actually%20entirely%20intelligible%2C%20way%20to%20accomplish%20some%20remarkable%20combinations%20of%20calculations%20that%20hitherto%20were%20separated%20into%20multiple%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120743%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120743%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ELET%3C%2FSTRONG%3E%20provides%20a%20level%20of%20inbuilt%20documentation%20(roughly%20equivalent%20to%20a%20helper%20range%20with%20adjacent%20annotation)%20but%20the%20name%20is%20a%20slight%20penalty%20in%20terms%20of%20formula%20length%20if%20its%20formula%20is%20used%20just%20once.%26nbsp%3B%20In%20terms%20of%20its%20function%2C%20it%20is%20precisely%20the%20same%20as%20the%20nested%20formula%20that%20would%20result%20from%20substituting%20the%20formula%20in%20place%20of%20the%20name%20where%20it%20used.%26nbsp%3B%20I%20find%20the%20sequential%20form%20easier%20to%20follow%20than%20deeply%20nested%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20computational%20advantage%2C%20as%20well%20as%20formula%20length%20gain%2C%20if%20the%20named%20formula%20is%20used%20multiple%20times%20as%20in%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20IF(ROW(INDIRECT(%22A1%3AA%22%26amp%3B1%2BROWS(array)))%26lt%3B1%2BROWS(array)%2C%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3BINDEX(array%2C%20ROW(INDIRECT(%22A1%3AA%22%26amp%3B1%2BROWS(array)))%20)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3Bscalar)%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhich%20could%20be%20used%20in%20place%20of%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20IF(k%3CN%3E%3C%2FN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20tradition%20Excel%20techniques%2C%20the%20things%20I%20had%20in%20mind%20were%3C%2FP%3E%3CP%3E1.%20the%20direct%20referencing%20of%20cells%3C%2FP%3E%3CP%3E2.%20the%20use%20of%20filled%20formulas%20with%20relative%20referencing%3C%2FP%3E%3CP%3E3.%20a%20reluctance%20to%20use%20array%20formulas%3C%2FP%3E%3CP%3E4.%20the%20use%20of%20deeply%20nested%20formulas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20built-in%20functions%2C%20with%20a%20few%20notable%20exceptions%20provide%20an%20area%20of%20commonality%20between%20traditional%20working%20practices%20and%20those%20predicated%20on%20dynamic%20arrays.%26nbsp%3B%20Now%20with%20the%20Lambda%20function%20Excel%20is%20entering%20the%20world%20of%20being%20a%20full-on%20programming%20platform.%26nbsp%3B%20The%20way%20in%20which%20one%20conceives%20solutions%20changes%20completely.%26nbsp%3B%20I%20just%20hope%20the%20users%20are%20up%20to%20it!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120518%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120518%22%20slang%3D%22en-US%22%3EMy%20analysis%20of%20the%20LET%20syntax%20reveals%20that%20it%20defines%20a%20name%20for%20the%20value%20in%20an%20argument%2C%20then%20use%20such%20name%20in%20the%20main%20formula.%20If%20such%20value%2C%20which%20has%20now%20been%20defined%20as%20a%20name%2C%20won't%20be%20reused%20in%20the%20main%20formula%2C%20such%20definition%20becomes%20superfluous!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120380%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120380%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20all%20going%20so%20well!%26nbsp%3B%20Because%20the%20formulae%20used%20in%20the%20calculation%20prevented%20the%20function%20being%20called%20with%20the%20array%20of%205%20products%2C%20I%20set%20out%20to%20use%20recursion%20to%20build%20the%20solution%20array.%3C%2FP%3E%3CP%3EThis%20involved%20a%20wrapper%20function%20FOREACH%20that%20builds%20the%20recursion%20stack%20and%20XSTACK%20that%20extracts%20results%20following%20the%20return%20from%20each%20level.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253922iE3FAE6FDF190026D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20returned%20the%20array%20of%20inventory%20values%20as%20required.%26nbsp%3B%20Then%20I%20tried%20to%20SUM%20the%20amounts%20directly.%26nbsp%3B%20It%20turns%20out%20that%20the%20result%20is%20not%20recognised%20as%20an%20array%2C%20despite%20the%20fact%20that%20it%20may%20be%20output%20as%20an%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120269%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3Ewrote%20%3CEM%3EUsing%20LET%20is%20analogous%20to%20%22legal%20cheating%22%20for%20allowing%20the%20definition%20of%20names%20outside%20the%20Name%20Manager.%20For%20the%20purpose%20of%20my%20formula%20challenge%2C%20I%20will%20allow%20such%2C%20not%20because%20I%20allow%20cheating%2C%20but%20because%20it%20is%20legal.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20one%20way%20of%20looking%20at%20LET.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20thinking%20of%20LET%20more%20as%20a%20way%20of%20using%20what%20we%20often%20refer%20to%20as%20%3CSTRONG%3E%3CU%3E%22helper%20columns%2C%22%3C%2FU%3E%3C%2FSTRONG%3E%20just%20that%20all%20of%20those%20%22helper%20columns%22%20end%20up%20being%20incorporated%20into%20(or%20referenced%20within)%20a%20single%20formula.%20In%20fact%2C%20that%20has%20given%20me%20insight%20into%20how%20to%20use%20LET%20more%20extensively%2C%20whenever%20tempted%20toward%20the%20%22helper%20column%22%20approach.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120235%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EDeleted%20previous%20reply%2C%20getting%20used%20to%20the%20set-up%20on%20the%20forum.%20I've%20used%20LET%20below%20for%20readability%20and%20in%20the%20first%20cell%20in%20the%20attached%2C%20but%20left%20other%20formulas%20using%20older%20functions.%20On%20the%20right%20track%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLET(%3CBR%20%2F%3EProduct%2C%22Courage%22%2C%3CBR%20%2F%3EProdFilter%2C(ProductName%3DProduct)%2C%3CBR%20%2F%3EPurchFilter%2C(TrnType%3D%22Purchase%22)%2C%3CBR%20%2F%3EInventoryOnHand%2CSUM(CHOOSE(MATCH(TrnType%2C%7B%22Purchase%22%3B%22Sale%22%7D%2C0)%2C1%2C-1)*ProdFilter*TrnQty)%2C%3CBR%20%2F%3ELastPurchasePrice%2CINDEX(TrnPrice%2CMAX(TrnNum*PurchFilter*ProdFilter))%2C%3CBR%20%2F%3ELastPurchaseQty%2CINDEX(TrnQty%2CMAX(TrnNum*PurchFilter*ProdFilter))%2C%3CBR%20%2F%3ELastPurchaseInv%2CMIN(InventoryOnHand%2CLastPurchaseQty)%2C%3CBR%20%2F%3EPrevPurchasePrice%2CINDEX(TrnPrice%2CLARGE(TrnNum*PurchFilter*ProdFilter%2C2))%2C%3CBR%20%2F%3EPrevPurchInv%2C(InventoryOnHand-LastPurchaseInv)%2C%3CBR%20%2F%3ELastPurchaseInv*LastPurchasePrice%2BPrevPurchInv*PrevPurchasePrice)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120223%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120223%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20simply%20adopted%20the%20adjective%20%22traditional%22%20mentioned%20by%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%20.%3CBR%20%2F%3EPerhaps%2C%20%22traditional%20functions%22%20would%20refer%20to%20those%20which%20are%20not%20available%20to%20non-Microsoft%20365%20users.%20Using%20LET%20is%20analogous%20to%20%22legal%20cheating%22%20for%20allowing%20the%20definition%20of%20names%20outside%20the%20Name%20Manager.%20For%20the%20purpose%20of%20my%20formula%20challenge%2C%20I%20will%20allow%20such%2C%20not%20because%20I%20allow%20cheating%2C%20but%20because%20it%20is%20legal.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120117%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20the%20explanation.%20Still%20had%20no%20time%20to%20check%20in%20details%2C%20will%20do.%3C%2FP%3E%0A%3CP%3EQuestion%20to%20your%20latest%20post%2C%20related%20to%20this%20case%20what%20do%20you%20mean%20under%20traditional%20functions%3F%20If%20people%20have%20LET()%20they%20shall%20have%20SORT()%2C%20FILTER()%2C%20etc.%20If%20only%20LAMBDA()%20is%20still%20exotic%2C%20but%20here%20it%20is%20only%20cosmetic%20on%20the%20top%20of%20LET().%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120050%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120050%22%20slang%3D%22en-US%22%3EI'm%20already%20accustomed%20to%20your%20inherent%20aversion%20of%20using%20direct%20cell%20references.%20Even%20so%2C%20I'm%20impressed%20at%20your%20improvement%20on%20the%20solution%20of%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20.%3CBR%20%2F%3EAs%20I%20had%20previously%20advocated%2C%20traditional%20formulas%20are%20accessible%20to%20more%20users%20than%20modern%20Excel%20functions.%20For%20that%20reason%2C%20my%20formula%20challenges%20reach%20the%20widest%20possible%20audience%2C%20rather%20than%20a%20privileged%20few.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119980%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20had%20the%20audacity%20to%20work%20on%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B's%20version%20of%20the%20solution.%26nbsp%3B%20My%20reason%20for%20doing%20this%20was%20that%20I%20wanted%20to%20see%20how%20easy%20(or%20difficult)%20collaborative%20development%20might%20be.%26nbsp%3B%20My%20first%20finding%20was%20that%20a%20utility%20that%20steps%20through%20the%20local%20names%20within%20a%20LET%20function%20returning%20their%20values%20one%20by%20one%20would%20be%20of%20immense%20value.%26nbsp%3B%20The%20second%20conclusion%20is%20that%20working%20with%20someone%20else's%20LET%20formula%20is%2C%20indeed%2C%20easier%20than%20working%20with%20traditional%20formulas%20(such%20as%20we%20were%20meant%20to%20create%20for%20the%20present%20challenge).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20make%20a%20change%20to%20Sergei's%20formula%20in%20that%20I%20replaced%20the%20matrix%20multiplication%20MMULT%20by%20a%20simpler%20SUM.%26nbsp%3B%20To%20take%20the%20work%20forward%20I%20then%20wrapped%20the%20LET%20with%20LAMBDA%20to%20improve%20the%20parameter%20passing%20(I%20detest%20direct%20cell%20references%20in%20general%20and%20was%20never%20going%20to%20find%20one%20buried%20in%20the%20middle%20of%20a%20multi-line%20function%20appealing%2C%20despite%20its%20being%20appropriate%20to%20the%20challenge).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LAMBDA(prNm%2C%0A%20%20LET(%0A%20%20%20%20type%2C%20-(EVEN(TrnType%3D%22Sale%22)-1)%2C%0A%20%20%20%20prod%2C%20(ProductName%3DprNm)%2C%0A%20%20%20%20table%2C%20CHOOSE(%7B1%2C2%2C3%2C4%7D%2CTrnNum%2CTrnQty*type%2C%20TrnPrice%2C%20type*prod)%2C%0A%20%20%20%20total%2C%20SUM(INDEX(FILTER(table%2Cprod)%2C0%2C2))%2C%0A%20%20%20%20two%2C%20INDEX(SORT(FILTER(table%2C%20prod*type%3D1)%2C%2C-1)%2C%7B1%3B2%7D%2C%7B2%2C3%7D)%2C%0A%20%20%20%20price%2C%20INDEX(two%2C%2C2)%2C%0A%20%20%20%20lastQty%2C%20MIN(total%2C%20INDEX(two%2C1%2C1))%2C%0A%20%20%20%20QTYs%2C%20CHOOSE(%7B1%3B2%7D%2C%20lastQty%2C%20total-lastQty)%2C%0A%20%20SUM(QTYs*price))%0A%20%20)(%40Product)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20have%20yet%20to%20clarify%20thoughts%20on%20how%20the%20solutions%20to%20such%20problems%20might%20best%20be%20packaged.%26nbsp%3B%20One%20option%20might%20be%20to%20output%20the%20results%20to%20a%20table%20and%20create%20a%20rich%20data%20type%20with%20inventory%20quantities%20and%20values%20as%20attributes.%26nbsp%3B%20Another%20might%20be%20to%20use%20recursion%20to%20generate%20values%20for%20the%20five%20product%20lines%20as%20a%20single%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20look%20forward%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B's%20solution%20in%20due%20course.%26nbsp%3B%20It%20does%20me%20no%20harm%20to%20be%20shown%20that%20amazing%20solutions%20can%20be%20created%20with%20traditional%20techniques.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119906%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119906%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%20must%20not%20refer%20to%20any%20cell%20in%20the%20range%20%3CSTRONG%3EG1%3AL15%3C%2FSTRONG%3E.%20Nonetheless%2C%20you%20should%20refer%20to%20%3CSTRONG%3EJ18%3C%2FSTRONG%3E%20for%20your%20formula%20in%20%3CSTRONG%3EL18%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119887%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119887%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELOL%20-%20I%20missed%20the%20instructions%20in%20bold%20font.%20I%20think%20the%20first%20book%20is%20what%20you're%20looking%20for%2C%20the%20second%20includes%20some%20named%20formulas%20that%20may%20make%20it%20more%20readable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B-%20I%20corrected%20the%20workbook.%20With%20LET%2C%20I%20think%20it%20would%20look%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ELET(P%2C%22Purchase%2C%0A%20%20%20%20S%2C%22Sale%22%2C%0A%20%20%20%20COND%2C(TrnType%3DP)*(ProductName%3DJ18)%2C%0A%20%20%20%20INV%2CSUMPRODUCT((TrnType%3DP)-(TrnType%3DS)%2C--(ProductName%3DJ18)%2CTrnQty)%2C%0A%20%20%20%20LP%2CLOOKUP(2%2C1%2F((TrnType%3DP)*(ProductName%3DJ18))%2CTrnQty)%2C%0A%20%20%20%20PCT%2CINV%2FLP%2C%0A%20%20%20%20SUMPRODUCT(((MAX(0%2CPCT-1)*%7B1%2C0%7D%2BMIN(1%2CPCT)*%7B0%2C1%7D)*LP)*((TrnNum%3DLARGE(COND*TrnNum%2C%7B2%2C1%7D))*TrnPrice)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119780%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119780%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDon't%20look%20so%20sad%2C%20it's%20not%20yet%20over!%3C%2FSTRONG%3E%20We%20could%20derive%20the%20correct%20results%20using%20functions%20available%20since%20Excel%202010.%20So%2C%20your%20Excel%202016%20version%20has%20certainly%20available%20functions%20to%20solve%20the%20challenge.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20is%20limited%20to%20only%20the%20last%20two%20(2)%20purchases%3B%20otherwise%2C%20the%20accounting%20policy%20of%20buying%20products%20only%20at%20reorder%20point%20would%20be%20violated.%20Moreover%2C%20buying%20additional%20products%20despite%20not%20yet%20reaching%20the%20reorder%20point%20would%20be%20a%20foolish%20business%20decision.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThus%2C%20the%20calculation%20of%20the%20reorder%20point%20is%20crucial%20in%20determining%20whether%20to%20buy%20additional%20products%20now%20or%20defer%20the%20purchase%20decision%20to%20a%20later%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119775%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESadly%2C%20I'm%20still%20on%20office%202016%20and%20have%20few%2C%20if%20any%2C%20functions%20besides%20sumproduct%20that%20natively%20accept%20array%20arguments.%20Not%20using%20CSE%20or%20named%20formulas%20makes%20for%20a%20lot%20fewer%20tools%20in%20the%20toolbox.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20point%20of%20clarification%2C%20though.%20Do%20you%20intend%20for%20the%20formula%20to%20only%20analyze%20the%20last%20two%20entries%20or%20dynamic%3F%20Say%20the%20Sales%20were%20200%2C%20leaving%202411%20in%20inventory%3F%20It%20appears%20the%20expected%20results%20formula(s)%20are%20hardwired%20to%20only%20look%20at%20the%20last%20two%20purchases%20(but%20I%20know%20they%20are%20overly%20simplistic%20and%20not%20your%20actual%20solution).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119765%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119765%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20still%20unable%20to%20get%20SUMPRODUCT%20to%20work%20with%20the%20two%20variables%20named%20in%20the%20LET%20function.%20So%20I%20had%20to%20do%20it%20less%20elegantly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20definitely%20a%20learning%20experience%2C%20both%20in%20my%20own%20solution%2C%20but%20also%2C%20now%2C%20looking%20through%20how%20others%20have%20solved%20it.%20A%20great%20example%20of%20how%20Excel%20enables%20multiple%20routes%20to%20the%20same%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20real%20world%2C%20however%2C%20I%20doubt%20I'd%20be%20an%20advocate%20of%20doing%20it%20all%20in%20one%20formula%3B%20definitely%20wouldn't%20recommend%20it%20without%20LET.%20The%20LET%20function%20does%20enable%20readability%2C%20both%20by%20shortening%2C%20and%20by%20ultimately%20using%20names%20that%20make%20sense%20in%20subsequent%20formulas.%20I%20like%20for%20formulas%20to%20be%20intelligible%2C%20not%20only%20be%20the%20developer%2C%20but%20also%20by%20the%20less%20experienced...%20So%20helper%20columns%20come%20in%20handy%20for%20that.%20It'd%20be%20interesting%20to%20hear%20what%20others%20think.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119752%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119752%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEven%20without%20LET%2C%20we%20can%20still%20derive%20the%20correct%20results%20with%20a%20formula%20that%20conforms%20to%20the%20rules%20of%20the%20challenge.%20If%20you%20have%20the%20courage%20to%20comply%2C%20you%20can%20perhaps%20devise%20an%20obedient%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119750%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECongratulations!%20You%20have%20significantly%20improved%20your%20solution.%20Were%20you%20still%20unable%20to%20use%20SUMPRODUCT%20as%20you%20announced%20earlier%3F%20I%20did%20but%20I%20temporarily%20conceal%20its%20usage%20until%20I%20ultimately%20divulge%20my%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119743%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119743%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20clarify%20the%20logic%20of%20the%20challenge%2C%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Calculate%20the%20unsold%20quantity%20of%20the%20product%20by%20subtracting%20the%20sold%20quantity%20from%20the%20purchased%20quantity.%20For%20%22Courage%22%2C%20the%20purchased%20quantity%20of%202%2C611%20units%20minus%20the%20sold%20quantity%20of%202%2C430%20units%20equals%20the%20unsold%20quantity%20of%20181%20units.%20Absent%20any%20contrary%20evidence%20of%20loss%2C%20the%20unsold%20quantity%20must%20be%20equal%20to%20the%20physical%20count.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Determine%20the%20quantity%20of%20the%20product%20from%20the%20last%20purchase.%20For%20%22Courage%22%2C%20such%20quantity%20is%20101%20units%20from%20Transaction%2031%20at%2076.00%20per%20unit.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20Determine%20the%20lower%20value%20between%20the%20physical%20count%20and%20the%20quantity%20from%20the%20last%20purchase.%20For%20%22Courage%22%2C%20the%20lower%20value%20between%20the%20physical%20count%20of%20181%20units%20and%20the%20quantity%20from%20the%20last%20purchase%20of%20101%20units%20is%20obviously%20the%20latter.%20Thus%2C%20of%20the%20181%20units%20counted%20physically%2C%20101%20units%20originate%20from%20the%20last%20purchase%20of%20101%20units%20from%20Transaction%2031%20at%2076.00%20per%20unit%20while%20the%20remaining%2080%20units%20originate%20from%20the%20second%20to%20the%20last%20purchase%2C%20which%20is%20760%20units%20from%20Transaction%2022%20at%2065.00%20per%20unit.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20%22Integrity%22%2C%20the%20lower%20value%20between%20the%20physical%20count%20of%20170%20units%20and%20the%20quantity%20from%20the%20last%20purchase%20of%20550%20units%20is%20obviously%20the%20former.%20Thus%2C%20the%20entire%20170%20units%20originate%20from%20only%20the%20last%20purchase%20of%20550%20units%20from%20Transaction%2040%20at%2020.00%20per%20unit.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E4.%20Determine%20the%20value%20of%20the%20physically%20counted%20inventory%20under%20the%20FIFO%20method%20by%20multiplying%20the%20dissected%20units%20by%20their%20corresponding%20purchase%20prices.%20For%20%22Courage%22%2C%20(101%20x%2076.00)%20%2B%20(80%20x%2065.00)%20%3D%2012%2C876.00.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20%22Integrity%22%2C%20(170%20x%2020.00)%20%3D%203%2C400.00.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAmazed%20by%20how%20I%20generate%20these%20formula%20exercises%3F%20These%20exercises%20are%20%3CEM%3Ereal%20business%20circumstances%3C%2FEM%3E%20included%20in%20my%20textbooks%20on%20%3CSTRONG%3EEXCELlent%20Financial%20Accounting%20and%20Reporting%3C%2FSTRONG%3E!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119739%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119739%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInteresting!%20I%20noticed%20an%20error%20in%20the%20solution%20I%20posted%20earlier.%20It%20didn't%20adversely%20affect%20the%20result%20(although%20it%20might%20have%20under%20some%20other%20conditions)%2C%20but%20it%20was%20wasted%20characters%20in%20that%20they%20added%20no%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20corrected%20(and%20more%20streamlined)%20version.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3E%3DLET(%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%3CSTRONG%3EPQt%2CFILTER(TrnQty%2C(ProductName%3DJ18)*(TrnType%3D%22Purchase%22))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESQt%2CFILTER(TrnQty%2C(ProductName%3DJ18)*(TrnType%3D%22Sale%22))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ENQt%2CSUM(PQt)-SUM(SQt)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELPuR%2CCOUNT(PQt)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EARQtPr%2CFILTER(TrnQty%3ATrnPrice%2C(ProductName%3DJ18)*(TrnType%3D%22Purchase%22))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELPuQ%2CMIN(NQt%2CINDEX(ARQtPr%2CLPuR%2C1))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELPuP%2CINDEX(ARQtPr%2CLPuR%2C2)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESLPuQ%2CMIN(NQt-LPuQ%2CINDEX(ARQtPr%2CLPuR-1%2C1))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESLPuP%2CINDEX(ARQtPr%2CLPuR-1%2C2)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3E(LPuQ*LPuP)%2B(SLPuQ*SLPuP))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119735%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOK%2C%20if%20permitted%2C%20I'm%20going%20to%20withdraw%20my%20resignation%20from%20the%20match.%20I%20stuck%20it%20out%20after%20all%20(one%20thing%20that%20helped%20a%20lot%3A%20I%20learned%20how%20to%20do%20line%20breaks%20in%20the%20Formula%20Bar%2C%20which%20makes%20it%20a%20lot%20more%20readable%2C%20as%20all%20of%20you%20know).%20Here's%20the%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3E%3DLET(%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%3CSTRONG%3EPQt%2CFILTER(TrnQty%2C(ProductName%3DJ18)*(TrnType%3D%22Purchase%22))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESQt%2CFILTER(TrnQty%2C(ProductName%3DJ18)*(TrnType%3D%22Sale%22))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ENQt%2CSUM(PQt)-SUM(SQt)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELPuR%2CCOUNT(PQt)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EARQtPr%2CFILTER(TrnQty%3ATrnPrice%2C(ProductName%3DJ18)*(TrnType%3D%22Purchase%22))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELPuQ%2CMIN(NQt%2CINDEX(ARQtPr%2CLPuR%2C1))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELPuP%2CMIN(NQt%2CINDEX(ARQtPr%2CLPuR%2C2))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESLPuQ%2CMIN(NQt-LPuQ%2CINDEX(ARQtPr%2CLPuR-1%2C1))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESLPuP%2CMIN(NQt%2CINDEX(ARQtPr%2CLPuR-1%2C2))%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3E(LPuQ*LPuP)%2B(SLPuQ*SLPuP))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20not%20make%20SUMPRODUCT%20work%20with%20the%20last%20four%20%22name%20values%22%20--%20any%20idea%20why%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119722%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119722%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20define%20%3CA%20title%3D%22Examples%20of%20Array%20Manipulation%22%20href%3D%22https%3A%2F%2Fexcelxor.com%2Fcategory%2Fmanipulating-arrays%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Earray%20manipulation%3C%2FA%3E%20as%20the%20skillful%20control%20of%20formula%20results.%20Nonetheless%2C%20the%20challenge%20is%20to%20derive%20the%20correct%20results%2C%20regardless%20of%20the%20functions%20used%20thereby.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119650%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119650%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20against%20the%20ground%20rules%2C%20but%20since%20I%20don't%20have%20LET%20I%20did%20use%20named%20formulas.%20Including%20the%20length%20of%20those%20it%20is%20313%20characters%20(264%20if%20I%20had%20only%20used%201%20character%20for%20each%20name%2C%20neither%20accounts%20for%20the%20characters%20LET%20would%20have%20required).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlthough%20the%20challenge%20specifically%20called%20for%20getting%20amounts%20from%20the%20last%20two%20purchases%2C%20I%20modified%20it%20so%20the%20next%20N%20units%20for%20which%20to%20get%20the%20cost%20is%20variable%20(even%20though%20you%20have%20181%20in%20inventory%2C%20what%20is%20the%20cost%20of%20the%20next%205%2C%20for%20example).%20So%2C%20instead%20of%20pulling%20from%20the%20bottom%20up%20when%20N%20is%20less%20than%20the%20net%20inventory%20(LIFO)%2C%20it%20should%20still%20pull%20from%20the%20top%20down%20(FIFO%20-%20and%20accounting%20for%20the%20units%20already%20sold).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119579%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119579%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20guys%2C%20if%20this%20were%20a%20chess%20match%20(which%2C%20metaphorically%2C%20it%20is)%20I%20would%20tip%20my%20king%20on%20its%20side%2C%20and%20reach%20out%20to%20shake%20your%20hands.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20truly%20had%20fun%20learning%20how%20to%20nest%20FILTER%20within%20INDEX%20and%20COUNT%20(among%20others)%2C%20have%20formulas%20that%20will%20get%20each%20of%20the%20salient%20numbers%20here%2C%20and%20could%20write%20a%20LET%20to%20define%20all%20the%20re-used%20array%20formulations%2C%20but%20my%20head%20is%20starting%20to%20feel%20as%20it%20it%20might%20explode%20just%20from%20contemplating%20putting%20them%20%3CSTRONG%3Eall%3C%2FSTRONG%3E%20into%20a%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3E%3CU%3E%3CSTRONG%3Esingle%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FEM%3E%3C%2FFONT%3E%20formula%2C%20as%20Robert%20has%20stipulated%2C%20to%20say%20nothing%20of%20actually%20doing%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20it's%20been%20a%20learning%20experience%20for%20sure%2C%20and%20I%20look%20forward%20to%20applying%20my%20learnings.%20I'll%20keep%20looking%20over%20your%20shoulders%20in%20months%20ahead.%20I%20AM%20going%20to%20spend%20some%20time%20studying%20each%20of%20your%20solutions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119553%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERobert%2C%20I%20was%20going%20to%20apologise%20for%20using%20your%20challenges%20to%20explore%20the%20developing%20product%20that%20is%20Excel%20and%20learning%20new%20techniques%20rather%20than%20addressing%20the%20terms%20of%20the%20challenge%20head%20on.%26nbsp%3B%20It%20appears%20that%20I%20may%20not%20be%20alone%20in%20this!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20830px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253726i81D6606B97755DA3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20did%20even%20consider%20using%20recursion%20to%20keep%20a%20running%20total%20of%20stock%20value%20transaction%20by%20transaction%20but%20came%20to%20the%20conclusion%20that%20it%20would%20be%20too%20heavy%20in%20terms%20of%20its%20computation%20and%20would%20represent%20overkill.%26nbsp%3B%20I%20also%20wondered%20whether%20there%20is%20opportunity%20for%20a%20greater%20level%20of%20nested%20Lambda%20functions%20to%20expose%20the%20overarching%20logic%20to%20a%20greater%20extent%20but%20getting%20my%20brain%20to%20shift%20gears%20is%20not%20coming%20easily!%26nbsp%3B%20I%20had%20a%20look%20at%20some%20theory%20but%20the%20function%20representing%20recursion%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CEM%3E%CE%BB%3C%2FEM%3E(%3CEM%3E%CE%BBx.x%20x%3C%2FEM%3E)%20(%3CEM%3E%CE%BBx.x%20x%3C%2FEM%3E)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Edoes%20not%20come%20easily%20to%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119179%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119179%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20do%20you%20generate%20these%20exercises%3F%20Amazing!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20I%20again%20didn't%20understand%20the%20logic%20behind%20(From%20Last%20Purchase%20Qty)%2C%20just%20imitate%20your%20formula.%20First%20attempt%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0A%20%20type%2C%20-(EVEN(TrnType%3D%22Sale%22)-1)%2C%0A%20%20prod%2C%20(ProductName%3DJ18)%2C%0A%20%20table%2C%20CHOOSE(%7B1%2C2%2C3%2C4%7D%2CTrnNum%2CTrnQty*type%2C%20TrnPrice%2C%20type*prod)%2C%0A%20%20total%2C%20SUM(INDEX(FILTER(table%2Cprod)%2C0%2C2))%2C%0A%20%20two%2C%20INDEX(SORT(FILTER(table%2C%20prod*type%3D1)%2C%2C-1)%2C%7B1%3B2%7D%2C%7B2%2C3%7D)%2C%0A%20%20price%2C%20INDEX(two%2C%2C2)%2C%0A%20%20lastQty%2C%20MIN(total%2C%20INDEX(two%2C1%2C1))%2C%0A%20%20QTYs%2C%20CHOOSE(%7B1%2C2%7D%2C%20lastQty%2C%20total-lastQty)%2C%0AMMULT(QTYs%2Cprice)%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20logic%20will%20be%20more%20clear%20perhaps%20will%20do%20another%20attempt%20in%20few%20days.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119000%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119000%22%20slang%3D%22en-US%22%3E%3CP%3EWell%20done%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI''m%20still%20working%20on%20it%20(in%20my%20head%20mostly)...later%20on%20today%20I%20will%20be%20home%20where%20I%20have%20access%20to%20two%20more%20reasonable%20screens%2C%20rather%20than%20just%20a%20small%20MacBook%20Air....%20that%20should%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20nice%20if%20one%20of%20us--it%20won't%20be%20me--were%20to%20come%20up%20with%20a%20LAMBDA%20based%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2118893%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2118893%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BDon't%20really%20now%20what%20%22Array%20manipulation%22%20is%20and%20I'm%20probably%20violating%20all%20the%20rules%20of%20the%20challenge.%20Sorry%20about%20that.%26nbsp%3B%3CSPAN%3EI%20used%20your%20challenge%20to%20play%20around%20with%20LET%2C%20in%20stead.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2118001%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2118001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3CSTRONG%3EAn%20honor%2C%20yes%3B%20but%20undeserving%2C%20no!%3C%2FSTRONG%3E%20With%20your%20acceptance%2C%20I%20anticipate%20your%20valuable%20insights%20that%20will%20certainly%20augment%20our%20common%20quest%20for%20knowledge.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117990%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%2C%20my!%20It's%20an%20honor%20to%20be%20included%20among%20such%20august%20company%2C%20an%20honor%20I%20fear%20I%20don't%20deserve%20at%20all.%20I'm%20here%20to%20learn%2C%20and%20occasionally%20offer%20some%20more%20basic%20suggestions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20challenged%2C%20I'll%20give%20it%20a%20try%20later%20today.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2128332%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2128332%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wouldn't%20say%20that%20I%20have%20reached%20the%20point%20of%20being%20able%20to%20read%20the%20concise%20syntax%20that%20seems%20to%20be%20favoured%20in%20the%20world%20of%20functional%20analysis%20with%20any%20degree%20of%20fluency!%26nbsp%3B%20Even%20in%20the%20world%20of%20imperative%20computing%20I%20always%20preferred%20verbose%20to%20concise%20(Visual%20Basic%20to%20Java).%26nbsp%3B%20The%20Y%20combinator%20still%20leaves%20me%20struggling%20to%20read%20what%20is%20the%20function%20definition%20and%26nbsp%3Bwhat%20arguments%20are%20being%20processed.%26nbsp%3B%20With%20conventional%20math%2C%20which%20is%20somewhat%20concise%2C%20I%20instinctively%20feed%20in%20phases%20such%20as%20'with%20respect%20to'%20but%20it%20has%20yet%20to%20happen%20with%20Curry's%20notation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDespite%20that%2C%20I%20seem%20to%20have%20written%20a%20recursive%20Lambda%20function%20that%20I%20have%20applied%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B's%20challenge%20problem%20and%20then%20reapplied%20it%20to%20a%20simpler%20problem%20(that%20of%20calculating%20a%201D%20column%20that%20represents%20row%20totals%20of%20a%202D%20array)%20without%20change.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20997px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F254494i8D636DB930FF9FE7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIt%20might%20be%20that%20FOREACH%20implements%20the%20Y%20combinator%20and%20XSTACK%20the%20MAP%20function%20but%2C%20right%20now%2C%20I%20lack%20the%20clarity%20of%20thought%20to%20be%20sure.%26nbsp%3B%20For%20the%20sake%20of%20completeness%2C%20my%20function%20to%20sum%20rows%20was%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LAMBDA(arr%2Cnum%2C%0A%20%20%20%20SUM(INDEX(array%2Cnum%2C0))%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ENot%20totally%20awe%20inspiring%2C%20but%20useful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2128370%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2128370%22%20slang%3D%22en-US%22%3E%3CP%3EI%20forgot%20to%20say%2C%20the%20function%20call%20was%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20FOREACH(ROWS(array)%2C%20SUM1ROW%2C%20array)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20first%20parameter%20would%20appear%20to%20be%20superfluous%20but%20the%20variable%20it%20initialises%20served%20as%20a%20counter%20in%20order%20to%20terminate%20the%20recursion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBTW%20the%20formula%20you%20wrote%20for%20Currying%20the%20flow%20within%20the%20accumulation%20works%20(I%20assumed%20you%20had%20been%20in%20a%20position%20to%20test%20it).%26nbsp%3B%20Whether%20it%20serves%20the%20intended%20purpose%20of%20reducing%20memory%20demands%20or%20parameter%20count%2C%20I%20have%20yet%20to%20determine.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2129232%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2129232%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EManaged%20to%20reduce%20that%20legacy%20formula%20to%20256%20chars%20using%20identity%20above%26nbsp%3Band%20LOOKUP%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(INDEX(TrnPrice%2C%0AAGGREGATE(14%2C6%2C1%2F(ProductName%3DJ18)%2F(TrnType%3D%22Purchase%22)*TrnNum%2CIF(1%2C%7B1%3B2%7D))%2C0)*%0AMMULT(%7B1%2C0%3B-1%2C1%7D%2CABS(LOOKUP(2%2C1%2F(ProductName%3DJ18)%2F(TrnType%3D%22Purchase%22)%2CTrnQty)*%7B1%3B0%7D%2B%0A%7B1%2C-1%3B2%2C0%7D*SUM(TrnQty*-1%5E(TrnType%3D%22Sale%22)*(ProductName%3DJ18)))*%7B1%2C-1%7D%2F2))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(interesting%20as%20a%20challenge%20though%20hardly%20very%20readable!)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2130792%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2130792%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20was%20a%20%3CSTRONG%3Esignificant%2020%25%20reduction%20from%20your%20previous%20formula%20of%20320%20characters%3C%2FSTRONG%3E!%20You%20never%20fail%20to%20amaze%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2131282%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2131282%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20thank%20you%20for%20the%20challenge.%26nbsp%3B%20It%20is%20a%20demanding%20problem%20in%20its%20own%20right%20even%20before%20the%20'minimum%20key%20presses'%20aspect%20of%20the%20challenge.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20me%2C%20it%20has%20provided%20an%20opportunity%20to%20improve%20my%20understanding%20of%20Lambda%20functions%2C%20including%20setting%20up%20recursion%20to%20return%20an%20array%20of%20results.%26nbsp%3B%20It%20has%20also%20allowed%20me%20to%20identify%20what%20I%20believe%20is%20an%20error%20in%20the%20beta%20implementation%2C%20which%20I%20have%20reported.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20return%20to%20the%20theme%20of%20the%20challenge%20for%20a%20moment%2C%20a%20few%20more%20characters%20can%20be%20sweated%20out%20of%20Lori's%20formula%20by%20modifying%20the%20identification%20of%20transaction%20type.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUM(INDEX(TrnPrice%2CAGGREGATE(14%2C6%2C1%2F(ProductName%3DJ18)%2F(%3CFONT%20color%3D%22%23339966%22%3ETrnType%26lt%3B%22Q%22%3C%2FFONT%3E)*TrnNum%2CIF(1%2C%7B1%3B2%7D))%2C0)*MMULT(%7B1%2C0%3B-1%2C1%7D%2CABS(LOOKUP(2%2C1%2F(ProductName%3DJ18)%2F(%3CFONT%20color%3D%22%23339966%22%3ETrnType%26lt%3B%22Q%22%3C%2FFONT%3E)%2CTrnQty)*%7B1%3B0%7D%2B%3C%2FSTRONG%3E%3CSTRONG%3E%7B1%2C-1%3B2%2C0%7D*SUM(TrnQty*-1%5E(%3CFONT%20color%3D%22%23339966%22%3ETrnType%26gt%3B%22Q%22%3C%2FFONT%3E)*(ProductName%3DJ18)))*%7B1%2C-1%7D%2F2))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20also%20remove%20line-feeds%20to%20reduce%20the%20formula%20length%20to%20239.%26nbsp%3B%20My%20apologies%20for%20making%20such%20mundane%20changes%20once%20all%20the%20hard%20work%20has%20been%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2131332%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2131332%22%20slang%3D%22en-US%22%3EI%20did%20that%20in%20my%20solution%2C%20too.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2131876%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2131876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20excluded%20%22%3CEM%3Ediscernible%20substance%3C%2FEM%3E%22%20from%20the%20rules%20of%20the%20challenge%20because%20I%20didn't%20anticipate%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3Bcould%20devise%20another%20stunning%20formula%20that%2C%20though%20she%20indirectly%20admitted%20as%20lacking%20such%20substance%2C%20nonetheless%20returns%20the%20required%20result.%20Somehow%2C%20we%20may%20learn%20new%20techniques%20therefrom.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHad%20I%20included%20the%20foregoing%2C%20any%20formula%20solution%20must%20return%20the%20required%20result%20as%20the%20sum%20of%3A%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20The%20lower%20quantity%20between%20the%20physical%20count%20and%20the%20last%20purchase%2C%20multiplied%20by%20the%20price%20of%20the%20last%20purchase%3B%20and%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Any%20excess%20quantity%20of%20the%20physical%20count%20over%20the%20last%20purchase%2C%20multiplied%20by%20the%20price%20of%20the%20second%20to%20last%20purchase.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20emphatically%20deem%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3Bas%20the%20%3CEM%3Edoyenne%20of%20shortest%20formula%20challenges%3C%2FEM%3E.%20As%20such%2C%20I%20evaded%20%22%3CEM%3Eshortest%3C%2FEM%3E%22%20in%20the%20description%20hereof.%26nbsp%3B%20Inevitably%2C%20the%20ideal%20solution%20must%20be%20the%20%3CSTRONG%3Eshortest%20formula%20with%20discernible%20substance%3C%2FSTRONG%3E.%20Stated%20differently%2C%20such%20formula%20must%20be%20like%20a%20%3CSTRONG%3Eminiskirt%3C%2FSTRONG%3E%2C%20which%20is%20%3CEM%3Elong%20enough%20to%20cover%20the%20essentials%2C%20but%20short%20enough%20to%20invite%20attention%3C%2FEM%3E!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2131574%22%20slang%3D%22en-US%22%3ERe%3A%20FIFO%20Inventory%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2131574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20probably%20(undoubtedly)%20the%20least%20proficient%20person%20privileged%20to%20be%20included%20in%20this%20chaiN--least%20proficient%20in%20the%20Functions%20hidden%20away%20in%20the%20dark%20corridors%20of%20Excel%20manuals--I%20would%20like%20to%20go%20back%20and%20underscore%20the%20comment%20made%20by%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%20in%20her%20last%20submission.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReadability.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIntelligibility.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20needs%20to%20be%20a%20factor%20in%20all%20of%20this%20too%2C%20although%20I%20will%20certainly%20grant%20that%20it%20would%20be%20hard%20to%20measure.%20I%20definitely%20have%20learned%20from%20the%20challenge--and%20can%20accept%20readily%20that%20learning%2C%20stretching%2C%20growing%20may%20be%20the%20main%20goal.%20For%20me%20learning%20how%20to%20use%20LET%20in%20a%20more%20ambitious%20challenge%20was%20a%20transformative%20experience%3A%20I've%20gone%20back%20to%20one%20of%20my%20most%20important%20spreadsheets%20and%20re-written%20into%20a%20single%20LET%20formula%20a%20task%20that%20formerly%20involved%20two%20or%20three%20steps%2C%20an%20elaborate%20two-dimensional%20matrix%2C%20and%20a%20final%20multi-layered%20OFFSET.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20maybe%20my%20concern%20is%20more%20that%20when%20we're%20resolving%20some%20of%20the%20questions%20that%20get%20posed%20in%20this%20forum%20we%20take%20readability%20into%20account%2C%20taking%20a%20few%20moments%20to%20give%20more%20of%20an%20explanation%20of%20how%20and%20why%20the%20solution%20works.%20I%20know%20for%20myself%20that%20it's%20often%20the%20case%20that%20I%20think%20the%20%22why%20a%20solution%20works%22%20part%20is%20obvious.%20In%20some%20ways%2C%20for%20sure%2C%20it's%20unfortunate%20that%20Excel%20doesn't%20provide%20for%20side-by-side%20documentation%20within%20the%20many%20more%20powerful%20functions.%20It%20looks%20like%20it%20may%20in%20LAMBDA%20--%20I%20have%20yet%20to%20do%20other%20than%20read%20some%20of%20your%20fantastic%20implementations%20of%20that%20great%20new%20feature.%20If%20indeed%20side-by-side%20documentation%20is%20included%2C%20that%20is%20an%20important%20feature.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20clarity%20is%20a%20big%20part%20of%20the%20aversion%20expressed%20by%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%20for%20direct%20cell%20references%20and%20fully%20concur%3B%20I%20find%20myself%2C%20for%20that%20reason%2C%20often%20pointing%20out%20the%20dangers%20of%20hard-coding%20variables%20into%20formulas%2C%20given%20that%20variables%20often%20live%20up%20to%20their%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20suggestion%20for%20you%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%20%3A%20in%20your%20third%20year%20challenge%20consider%20including%20a%20criterion--along%20with%20successful%20results%20and%20shortest%20length--for%20the%20clearest%20explanation%20for%20the%20somewhat%20proficient%20Excel%20wanna-be.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20closing%2C%20though%2C%20let%20me%20express%20gratitude%20for%20this%20opportunity%20from%20%3CU%3Ethis%3C%2FU%3E%20somewhat%20proficient%20Excel%20wanna-be.%20Thanks%20for%20the%202021%20FIFO%20Inventory%20Challenge!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Trusted 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

 

79 Replies

@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.

@mathetes An honor, yes; but undeserving, no! With your acceptance, I anticipate your valuable insights that will certainly augment our common quest for knowledge.

@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.

Well done @Riny_van_Eekelen !

 

I''m still working on it (in my head mostly)...later on today I will be home where I have access to two more reasonable screens, rather than just a small MacBook Air.... that should help.

 

It would be nice if one of us--it won't be me--were to come up with a LAMBDA based solution.

@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!

image.png

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.

@Twifoo 

 

It's against the ground rules, but since I don't have LET I did use named formulas. Including the length of those it is 313 characters (264 if I had only used 1 character for each name, neither accounts for the characters LET would have required).

 

Although the challenge specifically called for getting amounts from the last two purchases, I modified it so the next N units for which to get the cost is variable (even though you have 181 in inventory, what is the cost of the next 5, for example). So, instead of pulling from the bottom up when N is less than the net inventory (LIFO), it should still pull from the top down (FIFO - and accounting for the units already sold).

@Riny_van_Eekelen 

I would define array manipulation as the skillful control of formula results. Nonetheless, the challenge is to derive the correct results, regardless of the functions used thereby. 

@Twifoo 

 

OK, if permitted, I'm going to withdraw my resignation from the match. I stuck it out after all (one thing that helped a lot: I learned how to do line breaks in the Formula Bar, which makes it a lot more readable, as all of you know). Here's the formula:

 

=LET(

PQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Purchase")),
SQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Sale")),
NQt,SUM(PQt)-SUM(SQt),
LPuR,COUNT(PQt),
ARQtPr,FILTER(TrnQty:TrnPrice,(ProductName=J18)*(TrnType="Purchase")),
LPuQ,MIN(NQt,INDEX(ARQtPr,LPuR,1)),
LPuP,MIN(NQt,INDEX(ARQtPr,LPuR,2)),
SLPuQ,MIN(NQt-LPuQ,INDEX(ARQtPr,LPuR-1,1)),
SLPuP,MIN(NQt,INDEX(ARQtPr,LPuR-1,2)),

(LPuQ*LPuP)+(SLPuQ*SLPuP))

 

 

I could not make SUMPRODUCT work with the last four "name values" -- any idea why?

@mathetes 

 

Interesting! I noticed an error in the solution I posted earlier. It didn't adversely affect the result (although it might have under some other conditions), but it was wasted characters in that they added no value.

 

Here's the corrected (and more streamlined) version.

 

=LET(

PQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Purchase")),
SQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Sale")),
NQt,SUM(PQt)-SUM(SQt),
LPuR,COUNT(PQt),
ARQtPr,FILTER(TrnQty:TrnPrice,(ProductName=J18)*(TrnType="Purchase")),
LPuQ,MIN(NQt,INDEX(ARQtPr,LPuR,1)),
LPuP,INDEX(ARQtPr,LPuR,2),
SLPuQ,MIN(NQt-LPuQ,INDEX(ARQtPr,LPuR-1,1)),
SLPuP,INDEX(ARQtPr,LPuR-1,2),

(LPuQ*LPuP)+(SLPuQ*SLPuP))

 

 

@Sergei Baklan 

Let me clarify the logic of the challenge, as follows:

 

1. Calculate the unsold quantity of the product by subtracting the sold quantity from the purchased quantity. For "Courage", the purchased quantity of 2,611 units minus the sold quantity of 2,430 units equals the unsold quantity of 181 units. Absent any contrary evidence of loss, the unsold quantity must be equal to the physical count. 

 

2. Determine the quantity of the product from the last purchase. For "Courage", such quantity is 101 units from Transaction 31 at 76.00 per unit. 

 

3. Determine the lower value between the physical count and the quantity from the last purchase. For "Courage", the lower value between the physical count of 181 units and the quantity from the last purchase of 101 units is obviously the latter. Thus, of the 181 units counted physically, 101 units originate from the last purchase of 101 units from Transaction 31 at 76.00 per unit while the remaining 80 units originate from the second to the last purchase, which is 760 units from Transaction 22 at 65.00 per unit. 

 

For "Integrity", the lower value between the physical count of 170 units and the quantity from the last purchase of 550 units is obviously the former. Thus, the entire 170 units originate from only the last purchase of 550 units from Transaction 40 at 20.00 per unit. 

 

4. Determine the value of the physically counted inventory under the FIFO method by multiplying the dissected units by their corresponding purchase prices. For "Courage", (101 x 76.00) + (80 x 65.00) = 12,876.00. 

 

For "Integrity", (170 x 20.00) = 3,400.00. 

 

Amazed by how I generate these formula exercises? These exercises are real business circumstances included in my textbooks on EXCELlent Financial Accounting and Reporting!

@mathetes 

Congratulations! You have significantly improved your solution. Were you still unable to use SUMPRODUCT as you announced earlier? I did but I temporarily conceal its usage until I ultimately divulge my solution.

@JMB17 

Even without LET, we can still derive the correct results with a formula that conforms to the rules of the challenge. If you have the courage to comply, you can perhaps devise an obedient solution.

@Twifoo 

 

Yes, still unable to get SUMPRODUCT to work with the two variables named in the LET function. So I had to do it less elegantly.

 

It was definitely a learning experience, both in my own solution, but also, now, looking through how others have solved it. A great example of how Excel enables multiple routes to the same solution.

 

In the real world, however, I doubt I'd be an advocate of doing it all in one formula; definitely wouldn't recommend it without LET. The LET function does enable readability, both by shortening, and by ultimately using names that make sense in subsequent formulas. I like for formulas to be intelligible, not only be the developer, but also by the less experienced... So helper columns come in handy for that. It'd be interesting to hear what others think.

@Twifoo 

 

Sadly, I'm still on office 2016 and have few, if any, functions besides sumproduct that natively accept array arguments. Not using CSE or named formulas makes for a lot fewer tools in the toolbox.

 

One point of clarification, though. Do you intend for the formula to only analyze the last two entries or dynamic? Say the Sales were 200, leaving 2411 in inventory? It appears the expected results formula(s) are hardwired to only look at the last two purchases (but I know they are overly simplistic and not your actual solution).

@JMB17 

Don't look so sad, it's not yet over! We could derive the correct results using functions available since Excel 2010. So, your Excel 2016 version has certainly available functions to solve the challenge. 

 

The formula is limited to only the last two (2) purchases; otherwise, the accounting policy of buying products only at reorder point would be violated. Moreover, buying additional products despite not yet reaching the reorder point would be a foolish business decision.

 

Thus, the calculation of the reorder point is crucial in determining whether to buy additional products now or defer the purchase decision to a later date.

@Twifoo 

 

LOL - I missed the instructions in bold font. I think the first book is what you're looking for, the second includes some named formulas that may make it more readable.

 

Edit: @Twifoo - I corrected the workbook. With LET, I think it would look like this:

LET(P,"Purchase,
    S,"Sale",
    COND,(TrnType=P)*(ProductName=J18),
    INV,SUMPRODUCT((TrnType=P)-(TrnType=S),--(ProductName=J18),TrnQty),
    LP,LOOKUP(2,1/((TrnType=P)*(ProductName=J18)),TrnQty),
    PCT,INV/LP,
    SUMPRODUCT(((MAX(0,PCT-1)*{1,0}+MIN(1,PCT)*{0,1})*LP)*((TrnNum=LARGE(COND*TrnNum,{2,1}))*TrnPrice)))

 

@JMB17 

Your formula must not refer to any cell in the range G1:L15. Nonetheless, you should refer to J18 for your formula in L18.