SOLVED

Profit Formula Challenge

%3CLINGO-SUB%20id%3D%22lingo-sub-1791880%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791880%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%3EThe%20thing%20that%20might%20have%20defeated%20me%20is%20the%20spirit%20of%20'No%20CSE'.%26nbsp%3B%20About%205%20years%20ago%20I%20had%20schooled%20myself%20to%20use%20CSE%20for%20almost%20all%20formulas%20in%20order%20to%20avoid%20filled%20formulas%20and%20relative%20referencing.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20developed%20the%20technique%20of%20using%20named%20formulas%20to%20avoid%20the%20dreaded%20implicit%20intersection%20and%20ensure%20calculations%20were%20performed%20correctly%20as%20arrays.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20it%20doesn't%20matter%20since%20I%20am%20using%20Excel%20365.%26nbsp%3B%20My%20formulas%20would%20not%20meet%20the%20criteria%20but%20...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20SUM(%20SignCashFlow%20*%20SUMIFS(Amounts%2C%20Types%2C%20listTypes%2C%20Branches%2C%20HdrBranches)%20)%0A%0A%3D%20LET(%0A%20%20crosstab%2C%20SUMIFS(Amounts%2C%20Types%2C%20listTypes%2C%20Branches%2C%20HdrBranches)%2C%0A%20%20OperatingProfit%2C%20MMULT(%20TRANSPOSE(SignCashFlow)%2C%20crosstab%20)%2C%0A%20%20COUNT(%20IF(OperatingProfit%20%26gt%3B%200%2C%201)%20)%20)%0A%0A%3D%20LET(%0A%20%20crosstab%2C%20SUMIFS(Amounts%2C%20Types%2C%20listTypes%2C%20Branches%2C%20HdrBranches)%2C%0A%20%20OperatingProfit%2C%20MMULT(%20TRANSPOSE(SignCashFlow)%2C%20crosstab%20)%2C%0A%20%20MinProfit%2C%20MIN(OperatingProfit)%2C%0A%20%20MaxProfit%2C%20MAX(OperatingProfit)%2C%0A%20%20XLOOKUP(%20MaxProfit%2C%20OperatingProfit%2C%20HdrBranches%20)%20)%0A%0A%3D%20LET(%0A%20%20crosstab%2C%20SUMIFS(Amounts%2C%20Types%2C%20listTypes%2C%20Branches%2C%20HdrBranches)%2C%0A%20%20OperatingProfit%2C%20MMULT(%20TRANSPOSE(SignCashFlow)%2C%20crosstab%20)%2C%0A%20%20MinProfit%2C%20MIN(OperatingProfit)%2C%0A%20%20MaxProfit%2C%20MAX(OperatingProfit)%2C%0A%20%20MaxProfit%20-%20MinProfit%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792047%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792047%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%2C%20in%20opposite%20I%20schooled%20myself%20to%20avoid%20CSE%20where%20possible.%20But%20by%20another%20formulas.%20Simplest%20cases%20SUMPRODUCT()%20instead%20of%20SUM()%2C%20MATCH%2FINDEX%20instead%20of%20MATCH%2C%20etc.%20Yes%2C%20I%20know%20about%20the%20names.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792135%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792135%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%3EA%20far%20more%20normal%20strategy%20I%20agree.%26nbsp%3B%20On%20the%20other%20hand%20SUMPRODUCT%20and%2C%20I%20think%2C%20AGGREGATE%20are%20array%20formulas%2C%20just%20no%20CSE.%26nbsp%3B%20It%20is%20mainly%20the%20user%20hostility%20to%20the%20presentation%20of%20CSE%20formulas%20that%20makes%20them%20viewed%20as%20methods%20of%20last%20resort%20-%20too%20clever%20by%20half!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELogically%20Excel%20should%20prevent%20a%20blank%20line%20from%20being%20inserted%20between%20a%20formula%20and%20any%20of%20its%20relative%20reference%20because%20the%20meaning%20is%20ambiguous%2C%20but%20that%20doesn't%20happen%20because%20in%20standard%20Excel%20practice%20the%20risk%20of%20error%20is%20less%20important%20that%20the%20fluency%20of%20the%20action-led%20user%20interface.%26nbsp%3B%20Instead%2C%20it%20is%20the%20array%20formula%20that%20blocks%20the%20insertion%20of%20rows%2C%20despite%20there%20being%20no%20such%20ambiguity.%26nbsp%3B%20I%20seem%20to%20remember%20a%20message%20such%20as%20'you%20cannot%20change%20part%20of%20an%20array%20formula'.%26nbsp%3B%20Since%20I%20cannot%20insert%20a%20blank%20cell%20into%20the%20middle%20of%20an%20array%20wouldn't%20it%20be%20more%20logical%20to%20assume%20I%20do%20not%20wish%20to%20and%20simple%20repaint%20the%20array%20unaltered%20just%20as%20a%20DA%20does%20now%20(an%20extra%20%23N%2FA!%20at%20the%20end%20if%20must%20be).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20an%20uphill%20grind%20to%20switch%20to%20array%20formulas%20but%20now%2C%20thankfully%2C%20the%20position%20is%20now%20reversed.%26nbsp%3B%20If%20I%20were%20forced%20to%20revert%20to%20'enter%20the%20formula%20in%20cell%20%24PQ17%20and%20fill%20down%201027%20cells'%2C%20I%20think%20I%20would%20just%20delete%20Excel%20and%20use%20a%20pocket%20calculator%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792160%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792160%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%0A%3CP%3EYes%2C%20arrays%20were%20all%20the%20time%2C%20just%20before%20in%20back-end%20and%20you%20shall%20to%20know%20what%20is%20behind%20and%20which%20formulas%20to%20use%2C%20now%20they%20are%20on%20front-end.%20Hope%20that's%20only%20the%20first%2C%20but%20significant%20one%2C%20step%20and%20we%20will%20be%20able%20to%20manipulate%20not%20only%20with%20entire%20array%20as%20an%20object%2C%20but%20with%20each%20element%20of%20it.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20I%20see%20nothing%20dramatic%20in%20Fill%20Down%2C%20doesn't%20matter%20on%20couple%20of%20cells%20or%20on%20couple%20thousand%20of%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792267%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792267%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%20could%20amplify%20why%20I%20feel%20single%20cell%20relative%20referencing%20is%20a%20slap-dash%20approach%2C%20though%20in%20keeping%20with%20the%20idea%20of%20end-user%20computing.%26nbsp%3B%20That%20would%20take%20the%20discussion%20too%20far%20from%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%26nbsp%3Bchallenge%20though%2C%20and%20I%20would%20like%20to%20see%20what%20others%20come%20up%20with.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792370%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792370%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%3Band%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%2C%20I%20edited%20my%20post%20to%20include%20two%20additional%20challenges%2C%20thereby%20bringing%20the%20total%20to%20eight.%20Please%20see%20the%20revised%20version%20of%20the%20file%20attached%20thereto.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792552%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792552%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%20confess%2C%20I%20have%20never%20seen%20or%20attempted%20a%20formula%20this%20large%20in%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20crosstab%2C%20SUMIFS(Amounts%2C%20Types%2C%20listTypes%2C%20Branches%2C%20HdrBranches)%2C%0A%20%20%20OperatingProfit%2C%20MMULT(%20TRANSPOSE(SignCashFlow)%2C%20crosstab%20)%2C%0A%20%20%20ProfitableBranches%2C%20COUNT(%20IF(OperatingProfit%20%26gt%3B%200%2C%201)%20)%2C%0A%20%20%20LossmakingBranches%2C%20COUNT(%20IF(OperatingProfit%20%26lt%3B0%2C%201)%20)%2C%0A%20%20%20MaxProfit%2C%20MAX(OperatingProfit)%2C%0A%20%20%20BestPerforming%2C%20XLOOKUP(%20MaxProfit%2C%20OperatingProfit%2C%20HdrBranches%20)%2C%0A%20%20%20MaxLoss%2C%20MIN(OperatingProfit)%2C%0A%20%20%20WorstPerforming%2C%20XLOOKUP(%20MaxLoss%2C%20OperatingProfit%2C%20HdrBranches%20)%2C%0A%20%20%20MinGain%2C%20MIN(IF(OperatingProfit%26gt%3B0%2C%20OperatingProfit)%20)%2C%0A%20%20%20MinGainBranch%2C%20XLOOKUP(%20MinGain%2C%20OperatingProfit%2C%20HdrBranches%20)%2C%0A%20%20%20MinLoss%2C%20MAX(IF(OperatingProfit%26lt%3B0%2C%20OperatingProfit)%20)%2C%0A%20%20%20MinLossBranch%2C%20XLOOKUP(%20MinLoss%2C%20OperatingProfit%2C%20HdrBranches%20)%2C%0A%20%20%20CHOOSE(%7B1%3B2%3B3%3B4%3B5%3B6%3B7%3B8%7D%2C%0A%20%20%20%20%20%20SUM(OperatingProfit)%2C%0A%20%20%20%20%20%20ProfitableBranches%2C%0A%20%20%20%20%20%20LossmakingBranches%2C%0A%20%20%20%20%20%20BestPerforming%2C%0A%20%20%20%20%20%20WorstPerforming%2C%0A%20%20%20%20%20%20MinGainBranch%2C%0A%20%20%20%20%20%20MinLossBranch%2C%0A%20%20%20%20%20%20MaxProfit-MaxLoss%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20it%20be%20better%20to%20break%20the%20calculation%20into%20parts%20even%20though%20it%20would%20mean%20recalculating%20the%20SUMIFS%20calculation%20within%20formula%3F%20I%20could%2C%20for%20example%2C%20have%20produced%20an%20array%20of%20MAX%2FMINs%20and%20then%20performed%20an%20XLOOKUP%20on%20the%20array.%3C%2FP%3E%3CP%3EAlternatively%2C%20should%20I%20have%20nested%20the%20MAX%2FMINs%20within%20the%20XLOOKUP%20to%20reduce%20the%20count%20of%20LET%20parameters%3F%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%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-1792750%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792750%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%2C%20me%20too!%20But%20would%20you%20believe%20me%20if%20I%20tell%20you%20each%20of%20my%20formulas%20did%20not%20exceed%20200%20characters%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792838%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792838%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%20faith%20in%20you%2C%20so%20yes%20I%20am%20prepared%20to%20believe%20the%20200%20characters.%26nbsp%3B%20I%20would%20also%20agree%20that%20conciseness%20is%20good%2C%20but%20it%20is%20not%20the%20only%20metric%20of%20relevance.%26nbsp%3B%20Comparing%20solutions%20will%20always%20have%20an%20element%20of%20subjectivity%20because%20the%20secondary%20objectives%20can%20be%20so%20different%20(getting%20the%20answer%20right%20is%20not%20so%20controversial).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EClearly%20my%20selection%20of%20names%20such%20as%20'OperatingProfit'%20and%20'LossmakingBranches'%20does%20not%20serve%20the%20cause%20of%20conciseness%20too%20well!%26nbsp%3B%20On%20the%20other%20hand%2C%20I%20did%20consider%20the%20number%20of%20floating%20point%20operations%20implicit%20in%20the%20formulas%20and%20it%20was%20that%20which%20led%20me%20to%20the%20unusual%20strategy%20of%20combining%20the%208%20distinct%20calculations%20into%20a%20single%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20formula%20appears%20to%20be%20950%20characters%20(including%20the%20140%20spaces%20used%20to%20improve%20layout).%26nbsp%3B%20That%20is%20not%20too%20bad%20when%20one%20considers%20that%20it%20delivers%20all%208%20calculations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20be%20interested%20to%20see%20your%20solution%20when%20you%20are%20ready%20to%20communicate%20it.%26nbsp%3B%20Did%20the%20'no%20CSE'%20rule%20impact%20you%20significantly%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792916%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792916%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%2C%20I%20agree%20with%20you%20that%20the%20choice%20of%20formulas%20is%20somehow%20a%20matter%20of%20personal%20preference%2C%20hence%20usually%20subjective.%20Like%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%2C%20I%20schooled%20myself%20to%20exploit%20the%20powers%20of%20functions%20that%20can%20natively%20process%20array%20operations.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1794877%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1794877%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%20hope%20my%20response%20has%20not%20put%20others%20off%20contributing%20traditional%20spreadsheet%20solutions!%3C%2FP%3E%3CP%3EThe%20solutions%20I%20am%20exploring%20are%20not%20available%20within%20most%20Excel%20versions%20and%20would%20not%20suit%20many%20users.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20new%20modification%20to%20my%20solution%2C%20I%20have%20made%20it%20more%20targeted%20and%20eliminated%20a%20number%3C%2FP%3E%3CP%3Eof%20intermediate%20variables.%26nbsp%3B%20The%20image%20below%20shows%20the%20spilt%20range%20output%20from%20a%20single%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%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%20465px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227632iB1B5004FD0660709%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%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20Crosstab%2C%20SUMIFS(Amounts%2C%20Types%2C%20listTypes%2C%20Branches%2C%20HdrBranches)%2C%0A%20%20OperatingProfit%2C%20MMULT(%20TRANSPOSE(SignCashFlow)%2C%20Crosstab%20)%2C%0A%20%20Profits%2C%20IF(OperatingProfit%20%26gt%3B%200%2C%20OperatingProfit)%2C%0A%20%20Losses%2C%20IF(OperatingProfit%26lt%3B%3D%200%2C%20-OperatingProfit)%2C%0A%0A%20%20%20KeyMetrics%2CHSTACK(%22%22%2C%20%0A%20%20%20%20%20SUM(OperatingProfit)%2C%20COUNT(Profits)%2C%20COUNT(Losses)%2C%20MAX(Profits)%2BMAX(Losses))%2C%0A%20%20%20ExtremeValues%2C%20HSTACK(%22%22%2C%20MAX(Profits)%2C%20MIN(Profits)%2C%20-MIN(Losses)%2C%20-MAX(Losses))%2C%0A%20%20%20AssociatedBranches%2C%20XLOOKUP(%20ExtremeValues%2C%20OperatingProfit%2C%20HdrBranches)%2C%0A%20%20%20VSTACK(%22%22%2C%20Headings1%2C%20KeyMetrics%2C%20Headings2%2C%20AssociatedBranches%2C%20ExtremeValues)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20have%20chosen%20to%20show%20formulas%20using%20Charles%20Williams's%20HSTACK%20and%20VSTACK%20functions%20because%20the%20intent%20is%20clearer%2C%20but%20CHOOSE%20with%20the%20correctly%20oriented%20index%20arrays%20will%20achieve%20the%20same%20result.%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-1795928%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1795928%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%3EAlthough%20you%20indirectly%20admitted%20that%20your%20solution%20is%20inherently%20inapplicable%20to%20most%20Excel%20users%2C%20it%20nonetheless%20behooves%20me%20to%20commend%20you%20for%20your%20tenacious%20pursuit%20thereof!%20Before%20I%20ultimately%20divulge%20my%20solutions%2C%20I%20deem%20it%20prudent%20to%20meanwhile%20anticipate%20possible%20solutions%20from%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%2C%20and%20other%20Excel%20Experts%20from%20this%20Community.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1796367%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1796367%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%2C%20sorry%2C%20perhaps%20some%20later%2C%20bit%20overloaded%20with%20current%20project%20and%20answer%20here%20only%20to%20have%20some%20rest%20and%20if%20that%20takes%20not%20more%20than%205%20minutes.%20Your%20task%20looks%20quite%20interesting%2C%20but%20I'm%20very%20far%20from%20accounting%20world%20and%20for%20me%20to%20understand%20what%20%22Combined%20bottom%20line%20of%20All%20Branches%22%20means%20will%20take%20significant%20time.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%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%20solutions%20looks%20interesting%2C%20but%20as%20I%20avoided%20CSE%20I%20try%20to%20avoid%20any%20macros%20and%20third-party%20add-ins.%20Charles%20suggests%20great%20additions%2C%20however%20I'd%20prefer%20to%20wait%20till%20the%20same%20will%20be%20out%20of%20the%20box.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1796568%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1796568%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%3EDon't%20worry%2C%20I%20empathize%20with%20you.%20%3CSTRONG%3EBottom%20Line%3C%2FSTRONG%3E%20simple%20means%20the%20%3CSTRONG%3Efinal%20amount%3C%2FSTRONG%3E%2C%20which%20refers%20to%20the%20values%20in%20the%20%3CSTRONG%3Erow%20for%20Operating%20Profit%3C%2FSTRONG%3E.%20Thus%2C%20%3CSTRONG%3ECombined%20Bottom%20Line%20of%20All%20Branches%3C%2FSTRONG%3E%20refers%20to%20the%20value%20for%20%3CSTRONG%3ETotal%20Operating%20Profit%3C%2FSTRONG%3E%2C%20which%20is%20%3CSTRONG%3E45%2C800.00%3C%2FSTRONG%3E.%20I%20trust%20that%20I%20have%20somehow%20clarified%20your%20doubts%20as%20to%20the%20terms%20used%20in%20this%20formula%20challenge.%20By%20the%20way%2C%20%3CSTRONG%3EAccounting%20is%20the%20language%20of%20business%3C%2FSTRONG%3E!%20It%20is%20only%20through%20Accounting%20that%20business%20information%20can%20be%20communicated%20to%20the%20users%20thereof.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1805618%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1805618%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%3ELike%20you%2C%20I%20avoid%20add-ins%20especially%20when%20sharing%20ideas%20with%20others.%26nbsp%3B%20%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F137306%22%20target%3D%22_blank%22%3E%40Charles%20Williams%3C%2FA%3E%26nbsp%3B%20work%20I%20have%20enjoyed%2C%20though%2C%20and%20I%20hope%20some%20of%20his%20ideas%20eventually%20get%20adopted%20by%20Microsoft.%26nbsp%3B%20I%20have%20found%20the%20array%20manipulation%20offered%20in%20the%20form%20of%20slicing%20and%20stacking%2C%20ACCUMULATE%20for%20accountancy%20corkscrew%20problems%20and%20some%20of%20the%20Regular%20Expression%20formulas%20to%20be%20particularly%20effective.%26nbsp%3B%20I%20did%20include%20a%20working%20version%20of%20the%20present%20formula%20based%20on%20CHOOSE%2C%20because%20people%20tend%20to%20be%20unimpressed%20when%20they%20open%20a%20workbook%20that%20refuses%20to%20calculate.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20Crosstab%2C%20SUMIFS(Amounts%2C%20Types%2C%20listTypes%2C%20Branches%2C%20HdrBranches)%2C%0A%20%20%20OperatingProfit%2C%20MMULT(%20TRANSPOSE(SignCashFlow)%2C%20Crosstab%20)%2C%0A%20%20%20Profits%2C%20IF(OperatingProfit%20%26gt%3B%200%2C%20OperatingProfit)%2C%0A%20%20%20Losses%2C%20IF(OperatingProfit%26lt%3B%3D%200%2C%20-OperatingProfit)%2C%0A%0A%20%20%20KeyMetrics%2C%20CHOOSE(%7B1%2C2%2C3%2C4%7D%2C%20SUM(OperatingProfit)%2C%20COUNT(Profits)%2C%20COUNT(Losses)%2C%20MAX(Profits)%2BMAX(Losses))%2C%0A%20%20%20ExtremeValues%2C%20CHOOSE(%7B1%2C2%2C3%2C4%7D%2C%20MAX(Profits)%2C%20MIN(Profits)%2C%20-%20MIN(Losses)%2C%20-MAX(Losses))%2C%0A%20%20%20AssociatedBranches%2C%20XLOOKUP(%20ExtremeValues%2C%20OperatingProfit%2C%20HdrBranches)%2C%0A%20%20%20CHOOSE(%7B1%3B2%3B3%3B4%3B5%7D%2C%20Headings1%2C%20KeyMetrics%2C%20Headings2%2C%20AssociatedBranches%2C%20ExtremeValues)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1807360%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1807360%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%0A%3CP%3ESo%2C%20we%20are%20almost%20in%20sync.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1807371%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1807371%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%2C%20thank%20you%20for%20the%20clarification.%20Yes%2C%20sure%20%2C%20accounting%20is%20the%20language%20of%20business%2C%20but%20English%20terms%20are%20not%20always%20understandable%20if%20your%20business%20uses%20another%20language.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1977492%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1977492%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%3CSPAN%3E1%20%3DSUM(F8%3AN8)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E2%20%3DSUM((F8%3AN8%26gt%3B0)*1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E3%20%3DSUM((F8%3AN8%26lt%3B0)*1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E4%20%3DSUM((F8%3AN8%3DMAX(F8%3AN8))*F1%3AN1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E5%20%3DSUM((F8%3AN8%3DMIN(F8%3AN8))*F1%3AN1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E6%20%3DSUM(MAX(F8%3AN8)%2C-MIN(F8%3AN8))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E7%20%3DSUM((F8%3AN8%3DMIN(IF((F8%3AN8%26lt%3B0)%2C%22%20%22%2C(F8%3AN8%26gt%3B0)*F8%3AN8)))*F1%3AN1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E8%20%3DSUM((F8%3AN8%3DMAX(IF((F8%3AN8%26gt%3B0)%2C%22%20%22%2C(F8%3AN8%26lt%3B0)*F8%3AN8)))*F1%3AN1)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22keenadvice_0-1607628066170.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F239663iFF15797B5BD4BBB7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22keenadvice_0-1607628066170.png%22%20alt%3D%22keenadvice_0-1607628066170.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPosted%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.linkedin.com%2Fin%2Fexcel-guy%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.linkedin.com%2Fin%2Fexcel-guy%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1978862%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1978862%22%20slang%3D%22en-US%22%3EKindly%20follow%20Rule%201.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1981289%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981289%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%231%20maybe%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(%3CBR%20%2F%3EAmounts%2C%3CBR%20%2F%3E(Types%3D%22Sales%20Revenues%22)%3CBR%20%2F%3E-(Types%3D%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2B(Types%3D%22Additional%20revenues%22)%3CBR%20%2F%3E-(Types%3D%22operating%20expenses%22)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20dynamic%20arrays%20in%20my%20version%20of%20Excel%20it%20works%20just%20as%20well%20with%20plain%20'Sum'%20at%20the%20beginning%20-%20no%20curly%20brackets%2C%20no%20%22Ctrl%22%20%22shift%22%20%22enter%22%20required%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20this%20also%20works%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3EAmounts*(%3CBR%20%2F%3E(Types%3D%22Sales%20Revenues%22)%3CBR%20%2F%3E-(Types%3D%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2B(Types%3D%22Additional%20revenues%22)%3CBR%20%2F%3E-(Types%3D%22operating%20expenses%22)%3CBR%20%2F%3E))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%232%20maybe%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Sales%20Revenues%22)%3CBR%20%2F%3E-SUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Additional%20Revenues%22)%3CBR%20%2F%3E-SUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E%26gt%3B0)*1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%20works%20with%20Sum%20at%20the%20start%2C%20on%20the%20later%20365%20versions%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIt%20seems%20to%20me%20there%20should%20be%20shorter%20versions%20possible%20-%20the%20formula%20above%20'feels'%20too%20long%20to%20me%2C%20but%20it's%20working%20on%20my%20computer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%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-1982705%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1982705%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%20think%20if%20you%20could%20show%20me%20how%20you've%20simplified%20the%20character%20length%20for%20%231%3C%2FP%3E%3CP%3EI%20would%20definitely%20learn%20something!!%3C%2FP%3E%3CP%3EAnd%20stand%20a%20good%20chance%20of%20getting%20the%20other%20solutions%20out!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%231%26nbsp%3B%40%2082%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3EAmounts%2C%3CBR%20%2F%3E-2*Amounts*((Types%3D%22Cost%20of%20Sales%22)%2B(Types%3D%22operating%20expenses%22))%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%231%26nbsp%3B%40%20118%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3EXLOOKUP(%3CBR%20%2F%3ETypes%2C%3CBR%20%2F%3E%24E%242%3A%24E%247%2C%3CBR%20%2F%3E1%2B(E2%3A%24E%247%3D%22cost%20of%20sales%22)*-2%2B(%24E%242%3A%24E%247%3D%22operating%20expenses%22)*-2)%3CBR%20%2F%3E*Amounts)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%231%26nbsp%3B%40%20120%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3ESUMIF(Types%2CUNIQUE(Types)%2CAmounts)%3CBR%20%2F%3E*(1-(UNIQUE(Types)%3D%22Operating%20Expenses%22)*2-(UNIQUE(Types)%3D%22Cost%20of%20Sales%22)*2)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%232%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1))%3CBR%20%2F%3E-2*SUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E-2*SUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E%26gt%3B0)*1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1982799%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1982799%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%3BHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIndeed%2C%20it%20is%20a%20wonderful%20initiative%20that%20is%20full%20of%20learning.%20I%20appreciate%20your%20Profit%20Challenge%20Post.%20Under%20the%20given%20criteria%20I%20could%20hardly%20do%20the%20first%20challenge.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((Amounts)*(Types%3D%7B%22Sales%20Revenues%22%2C%22Additional%20Revenues%22%7D))-SUMPRODUCT((Amounts)*(Types%3D%7B%22Cost%20of%20Sales%22%2C%22Operating%20Expenses%22%7D))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEagerly%20waiting%20for%20the%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1984751%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1984751%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%3E%3CEM%3ENothing%20magical%20needs%20to%20solve%20the%20issue%2C%20few%20I'm%20suggesting.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EN11%3A%26nbsp%3B%20%3DSUM(%24F%248%3A%24N%248)%3C%2FP%3E%3CP%3EN12%3A%26nbsp%3B%26nbsp%3B%3DCOUNTIF(%24F%248%3A%24N%248%2C%22%26gt%3B0%22)%3C%2FP%3E%3CP%3EN13%3A%26nbsp%3B%26nbsp%3B%3DCOUNTIF(%24F%248%3A%24N%248%2C%22%26lt%3B0%22)%3C%2FP%3E%3CP%3EN14%3A%26nbsp%3B%26nbsp%3B%3DSUMPRODUCT((%24F%248%3A%24N%248%3DMAX(%24F%248%3A%24N%248))*%24F%241%3A%24N%241)%3C%2FP%3E%3CP%3EN15%3A%26nbsp%3B%20%3DSUMPRODUCT((%24F%248%3A%24N%248%3DMIN(%24F%248%3A%24N%248))*%24F%241%3A%24N%241)%3C%2FP%3E%3CP%3EN16%3A%26nbsp%3B%26nbsp%3B%3DSUM(MAX(%24F%248%3A%24N%248)%2C-MIN(%24F%248%3A%24N%248))%3C%2FP%3E%3CP%3EN17%3A%26nbsp%3B%26nbsp%3B%3DSUMPRODUCT((%24F%248%3A%24N%248%3DMIN(IF((%24F%248%3A%24N%248%26lt%3B0)%2C%22%20%22%2C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20(%24F%248%3A%24N%248%26gt%3B0)*%24F%248%3A%24N%248)))*%24F%241%3A%24N%241)%3C%2FP%3E%3CP%3EN18%3A%26nbsp%3B%20%26nbsp%3B%3DSUMPRODUCT((%24F%248%3A%24N%248%3DMAX(IF((%24F%248%3A%24N%248%26gt%3B0)%2C%22%22%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20(%24F%248%3A%24N%248%26lt%3B0)*%24F%248%3A%24N%248)))*%24F%241%3A%24N%241)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EBut%20I%20would%20like%20to%20raise%20few%20valuable%20issues%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EFinding%20Best%2FWorst%20performing%20Branch%2C%20your%20exercise%20has%20NO%20BENCHMARK%2C%20considering%20this%20finding%204%20%26amp%3B%203%20are%20irrelevant.%3C%2FLI%3E%3CLI%3ESince%20you%20got%203%20for%20worst%20performing%20are%20negative%20values%2C%20and%20on%20same%20criteria%20best%20should%206%20!%3C%2FLI%3E%3CLI%3EBranch%20with%20Lowest%2FHighest%20operating%20profit%2FLoss%2C%26nbsp%3Bshould%20never%209%20%26amp%3B%202.%20Logically%20Lowest%20operating%20profit%20is%202100%2C%20and%20Lowest%20loss%20is%20-6800.%3C%2FLI%3E%3CLI%3EWhat%20is%20the%20use%20of%20finding%2038%2C700%2C%20no%20where%20utilized%20.%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1984825%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1984825%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20idea%20of%20this%20exercise%20is%20challenges%201-8%20using%20only%20raw%20data%20in%20columns%20A%3AC%2C%20more%20exactly%20using%20defined%20names%20(Rule%201).%20Thus%20you%20may%20forget%20for%20a%20while%20about%20numbers%20in%20F2%3AN8%2C%20they%20are%20only%20for%20the%20illustration%20and%20comparing%20the%20result.%3C%2FP%3E%0A%3CP%3EBenchmarks%20are%20defined%20in%20formulas%20for%20M11%3AM18%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1986108%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1986108%22%20slang%3D%22en-US%22%3EI%20would%20love%20to%20show%20you%20the%20simplification%20process%20but%20I%20would%20defeat%20the%20purpose%20of%20my%20challenge.%20Your%20solution%20to%20Challenge%201%20is%20almost%20the%20same%20as%20mine.%20The%20main%20difference%20is%20that%20I%20used%20the%20name%20%22Types%22%20only%20once!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1986129%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1986129%22%20slang%3D%22en-US%22%3EI%20will%20eventually%20divulge%20my%20solutions%20soon!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1992159%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1992159%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%3EColumn%20F%20has%20task%20%26amp%3B%20M%20has%20results%2C%2C%2C%20non%20of%20these%20are%20benchmark%2C%2C%2C%20like%20expected%20%22Best%20Performing%20Branch%22%20are%204%20but%20what%20is%20the%20lowest%20value%20to%20consider%20best%2C%2C%20So%20that%20about%20Worst%20are%203!!%20If%20consider%20the%20expected%20answer%20then%20%22Branch%20with%20Lowest%20Operating%20Profit%22%20are%20all%209%20never%20justifies%26nbsp%3BF8%3AN8%26gt%3B0%20since%20range%20has%20few%20negative%20values%20!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2003331%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2003331%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%3Bhere%20is%20my%20current%20set%20of%20solutions%20to%20%231%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20many%20characters%20does%20your%20solution%20have%20this%20down%20to%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%231a%3B%2077%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3EAmounts%3CBR%20%2F%3E*(1-2*((Types%3D%22cost%20of%20sales%22)%2B(Types%3D%22operating%20expenses%22)))%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20kind-of%20like%20this%20first%20one%20because%2C%20to%20me%2C%20it%20seems%20a%26nbsp%3B%20bit%20clearer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%231b%20repeats%20%22Types%22%20just%20once%20as%20requested%3B116%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3EAmounts%3CBR%20%2F%3E*SWITCH(Types%2C%22Sales%20Revenues%22%2C1%2C%22Cost%20of%20Sales%22%2C-1%2C%22Additional%20Revenues%22%2C1%2C%22Operating%20Expenses%22%2C-1)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20can%20be%20simplified%20by%20making%20use%20of%20IFERROR%20(rightly%20outlawed%20in%20my%20workplace%20for%20hiding%20all%20manner%20of%20sins)%3B%2084%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3EAmounts%3CBR%20%2F%3E*IFERROR(SWITCH(Types%2C%22Cost%20of%20Sales%22%2C-1%2C%22Operating%20Expenses%22%2C-1)%2C1)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%231c%20also%20repeats%20%22Types%22%20just%20once%20(but%2C%20I%20know%2C%20repeats%20the%20range)%3B%20110%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3EXLOOKUP(%3CBR%20%2F%3ETypes%2C%3CBR%20%2F%3E%24E%242%3A%24E%247%2C%3CBR%20%2F%3E1-2*((%24E%242%3A%24E%247%3D%22cost%20of%20sales%22)%2B(%24E%242%3A%24E%247%3D%22operating%20expenses%22)))%3CBR%20%2F%3E*Amounts)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20%231c%20you%20can%2C%20of%20course%2C%20play%20the%20same%20switch%20trick%20if%20you're%20prepared%20to%20live%20with%20a%20total%20of%20115%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3EXLOOKUP(%3CBR%20%2F%3ETypes%2C%3CBR%20%2F%3E%24E%242%3A%24E%247%2C%3CBR%20%2F%3EIFERROR(SWITCH(%24E%242%3A%24E%247%2C%22Cost%20of%20Sales%22%2C-1%2C%22Operating%20Expenses%22%2C-1)%2C1))%3CBR%20%2F%3E*Amounts)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20we%20also%20have%20%231d%20which%20is%20patently%20losing%20the%20competition%20at%20a%20super-massive%20119%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3ESUMIF(Types%2CUNIQUE(Types)%2CAmounts)%3CBR%20%2F%3E*IFERROR(SWITCH(UNIQUE(Types)%2C%22Cost%20of%20Sales%22%2C-1%2C%22Operating%20Expenses%22%2C-1)%2C1)%3CBR%20%2F%3E)%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%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%20can%20I%20respectfully%20ask%3A%3C%2FP%3E%3CP%3E-%20Which%20of%20the%20above%20do%20you%20see%20as%20the%20best%20of%20the%20bunch%3F%3C%2FP%3E%3CP%3E-%20How%20many%20characters%20do%20you%20have%20your%20solution%20to%20%231%20down%20to%3F%3C%2FP%3E%3CP%3E-%20Isn't%20it%20time%20you%20showed%20us%20all%20how%20much%20better%20your%20solution%20is%20compared%20to%20the%20above%3F!%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%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-1981946%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981946%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3Byou're%20right.%20Both%20of%20your%20formulas%20could%20be%20shortened%20by%20avoiding%20the%20repetition%20of%20Types%20in%20Challenge%201%20and%20SUMIFS%20in%20Challenge%202.%20As%20you'll%20see%20later%2C%20my%20solutions%20excluded%20such%20repetitions!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2004476%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2004476%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%3Ehere%20is%20my%20current%20set%20of%20solutions%20to%20%231%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20many%20characters%20does%20your%20solution%20have%20this%20down%20to%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%231a%3B%2077%20characters%3CBR%20%2F%3E%3DSUM(%3CBR%20%2F%3EAmounts%3CBR%20%2F%3E*(1-2*((Types%3D%22cost%20of%20sales%22)%2B(Types%3D%22operating%20expenses%22)))%3CBR%20%2F%3E)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20kind-of%20like%20this%20first%20one%20because%2C%20to%20me%2C%20it%20seems%20a%20bit%20clearer%3CBR%20%2F%3E%3CBR%20%2F%3E%231b%20repeats%20%22Types%22%20just%20once%20as%20requested%3B116%20characters%3CBR%20%2F%3E%3DSUM(%3CBR%20%2F%3EAmounts%3CBR%20%2F%3E*SWITCH(Types%2C%22Sales%20Revenues%22%2C1%2C%22Cost%20of%20Sales%22%2C-1%2C%22Additional%20Revenues%22%2C1%2C%22Operating%20Expenses%22%2C-1)%3CBR%20%2F%3E)%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20above%20can%20be%20simplified%20by%20making%20use%20of%20IFERROR%20(rightly%20outlawed%20in%20my%20workplace%20for%20hiding%20all%20manner%20of%20sins%3B%2084%20characters%3CBR%20%2F%3E%3DSUM(%3CBR%20%2F%3EAmounts%3CBR%20%2F%3E*IFERROR(SWITCH(Types%2C%22Cost%20of%20Sales%22%2C-1%2C%22Operating%20Expenses%22%2C-1)%2C1)%3CBR%20%2F%3E)%3CBR%20%2F%3E%3CBR%20%2F%3E%231c%20also%20repeats%20%22Types%22%20just%20once%20(but%2C%20I%20know%2C%20repeats%20the%20range)%3B%20110%20characters%3CBR%20%2F%3E%3DSUM(%3CBR%20%2F%3EXLOOKUP(%3CBR%20%2F%3ETypes%2C%3CBR%20%2F%3E%24E%242%3A%24E%247%2C%3CBR%20%2F%3E1-2*((%24E%242%3A%24E%247%3D%22cost%20of%20sales%22)%2B(%24E%242%3A%24E%247%3D%22operating%20expenses%22)))%3CBR%20%2F%3E*Amounts)%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20%231c%20you%20can%2C%20of%20course%2C%20play%20the%20same%20switch%20trick%20if%20you're%20prepared%20to%20live%20with%20a%20total%20of%20115%20characters%3CBR%20%2F%3E%3DSUM(%3CBR%20%2F%3EXLOOKUP(%3CBR%20%2F%3ETypes%2C%3CBR%20%2F%3E%24E%242%3A%24E%247%2C%3CBR%20%2F%3EIFERROR(SWITCH(%24E%242%3A%24E%247%2C%22Cost%20of%20Sales%22%2C-1%2C%22Operating%20Expenses%22%2C-1)%2C1))%3CBR%20%2F%3E*Amounts)%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20we%20also%20have%20%231d%20which%20is%20patently%20losing%20the%20competition%20at%20a%20super-massive%20119%20characters%3CBR%20%2F%3E%3DSUM(%3CBR%20%2F%3ESUMIF(Types%2CUNIQUE(Types)%2CAmounts)%3CBR%20%2F%3E*IFERROR(SWITCH(UNIQUE(Types)%2C%22Cost%20of%20Sales%22%2C-1%2C%22Operating%20Expenses%22%2C-1)%2C1)%3CBR%20%2F%3E)%3CBR%20%2F%3E%3CBR%20%2F%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%20can%20I%20respectfully%20ask%3A%3CBR%20%2F%3E-%20Which%20of%20the%20above%20do%20you%20see%20as%20the%20best%20of%20the%20bunch%3F%3CBR%20%2F%3E-%20How%20many%20characters%20do%20you%20have%20your%20solution%20to%20%231%20down%20to%3F%3CBR%20%2F%3E-%20Isn't%20it%20time%20you%20showed%20us%20all%20how%20much%20better%20your%20solution%20is%20compared%20to%20the%20above%3F!%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1791953%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791953%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%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%3C%2FP%3E%3CP%3EThe%20thing%20that%20might%20have%20defeated%20me%20is%20the%20spirit%20of%20'No%20CSE'.%26nbsp%3B%20About%205%20years%20ago%20I%20had%20schooled%20myself%20to%20use%20CSE%20for%20almost%20all%20formulas%20in%20order%20to%20avoid%20filled%20formulas%20and%20relative%20referencing.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20developed%20the%20technique%20of%20using%20named%20formulas%20to%20avoid%20the%20dreaded%20implicit%20intersection%20and%20ensure%20calculations%20were%20performed%20correctly%20as%20arrays.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20it%20doesn't%20matter%20since%20I%20am%20using%20Excel%20365.%26nbsp%3B%20My%20formulas%20would%20not%20meet%20the%20criteria%20but%20...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%20LET(%0A%20%20crosstab%2C%20SUMIFS(Amounts%2CTypes%2ClistTypes%2CBranches%2CHdrBranches)%2C%0A%20%20OperatingProfit%2C%20MMULT(TRANSPOSE(SignCashFlow)%2C%20crosstab)%2C%0A%20%20ProfitableBranches%2C%20COUNT(%20IF(OperatingProfit%26gt%3B0%2C%201))%2C%0A%20%20LossmakingBranches%2C%20COUNT(IF(OperatingProfit%26lt%3B0%2C%201))%2C%0A%20%20MinProfit%2C%20MIN(OperatingProfit)%2C%0A%20%20MaxProfit%2C%20MAX(OperatingProfit)%2C%0A%20%20BestPerforming%2C%20XLOOKUP(MaxProfit%2COperatingProfit%2CHdrBranches)%2C%0A%20%20WorstPerforming%2CXLOOKUP(MinProfit%2COperatingProfit%2CHdrBranches)%2C%0A%20%20CHOOSE(%7B1%3B2%3B3%3B4%3B5%3B6%7D%2C%0A%20%20%20%20SUM(OperatingProfit)%2C%0A%20%20%20%20ProfitableBranches%2C%0A%20%20%20%20LossmakingBranches%2C%0A%20%20%20%20BestPerforming%2C%0A%20%20%20%20WorstPerforming%2C%0A%20%20%20%20MaxProfit%20-%20MinProfit)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20avoids%20the%20need%20to%20repeat%20the%20SUMIFS%20step%20by%20retuning%20all%20six%20results%20as%20a%20single%20array.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2008080%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2008080%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%3EAfter%20some%20more%20fun%20time%20on%20this%20(and%20getting%20behind%20the%20rest%20of%20the%20family%20on%20re-runs%20of%20Lost%20on%20TV)%3C%2FP%3E%3CP%3EI've%20found%20that%20you%20can%20replace%20the%20core%20piece%20of%20the%20formula%20above%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20instead%20of%20using%20this%20for%20the%20%22ProfitArray%22%20above%2C%20at%20186%20characters%3A%3C%2FP%3E%3CP%3E%3DSUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1))%3CBR%20%2F%3E-2*(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20get%20the%20character%20count%20of%20the%20core%20piece%20of%20the%20formula%20down%20to%20131%3A%3C%2FP%3E%3CP%3E%3DMMULT(%3CBR%20%2F%3ETRANSPOSE(%3CBR%20%2F%3EIF(Branches%3DSEQUENCE(1%2C9%2C1%2C1)%2C1%2C0)*%3CBR%20%2F%3E(1%2B%3CBR%20%2F%3E(Types%3D%22Cost%20of%20Sales%22)*-2%3CBR%20%2F%3E%2B(Types%3D%22Operating%20Expenses%22)*-2%3CBR%20%2F%3E))%2C%3CBR%20%2F%3EAmounts)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ProfitChallenge.PNG%22%20style%3D%22width%3A%20966px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242172i3E800AF72355D10F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22ProfitChallenge.PNG%22%20alt%3D%22ProfitChallenge.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1791626%22%20slang%3D%22en-US%22%3EProfit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791626%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20believe%20you%20can%20comfortably%20manipulate%20arrays%2C%20then%20these%20eight%20formula%20challenges%20will%20test%20your%20mettle!%20I%20superbly%20love%20the%20sixth.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Twifoo_0-1608827170232.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242719i85F54A90B1752E56%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Twifoo_0-1608827170232.png%22%20alt%3D%22Twifoo_0-1608827170232.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-1791626%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-2014939%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2014939%22%20slang%3D%22en-US%22%3E%3CP%3EHi%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%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20really%20stunning%2C%20challenging%20and%20most%20importantly%20very%20much%20helpful.%3C%2FP%3E%3CP%3EI%20am%20sure%20if%20you%20participate%20in%20the%20Financial%20Modeling%20World%20Cup%2C%20you%20will%20lead%20the%20scoreboard.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2015242%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2015242%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%3EI'm%20so%20sorry.%20%3CSTRONG%3EI%20lied!%20%3C%2FSTRONG%3EAll%20my%20formulas%20are%20%3CSTRONG%3Eless%20than%20130%20characters!%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2016133%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2016133%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%20hope%20I%20did%20not%20appear%20to%20dismiss%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's%20achievement%20too%20lightly%3B%20that%20was%20not%20my%20intention!%26nbsp%3B%20I%20recognise%20the%20achievement%20of%20putting%20together%20a%20solution%20from%20AGGREGATE(%20%7B14%2C15%7D%2C%20...)%20%2C%20FREQUENCY(...)%2C%20MMULT(...)%20as%20something%20that%20few%20could%20manage%20(and%20a%20disproportionate%20number%20of%20those%20are%20probably%20to%20be%20found%20amongst%20your%20MVP%20peers).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20probably%20do%20go%20on%20too%20much%20promulgating%20my%20alternative%20view%20of%20spreadsheets%2C%20but%20it%20results%20from%20years%20of%20swimming%20against%20the%20tide%20of%20'tips%20and%20tricks'%2C%20that%20are%20primarily%20aimed%20at%20ways%20of%20creating%20spreadsheets%20faster%20(but%20not%20necessarily%20better).%26nbsp%3B%20I%20encountered%20dogmatic%20statements%20from%20financial%20modellers%20decrying%20the%20use%20of%20defined%20Names%20or%20array%20formula%20(e.g.%20the%20FAST%20standard).%26nbsp%3B%20At%20the%20same%20time%2C%20I%20have%20watched%20ModellOff%20contestants%20at%20work%20and%20admired%20the%20fluency%20with%20which%20they%20manipulate%20spreadsheets%20and%20freely%20admit%2C%20I%20couldn't%20come%20anywhere%20close.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%20formed%20a%20view%20that%20ran%20counter%20to%20most%20received%20wisdom%20(mainly%20regarding%20the%20use%20of%20named%20formulas%20and%20arrays)%20I%20campaigned%20for%20a%20few%20years%20(2016-2018)%20for%20an%20improved%20experience%20for%20manipulating%20arrays%2C%20in%20place%20of%20CSE%20which%20was%20dire.%26nbsp%3B%20I%20had%20envisaged%20an%20Array%20object%20(much%20like%20a%20table)%20which%20would%20respond%20dynamically%20to%20change.%26nbsp%3B%20I%20was%20assured%20that%20change%20was%20coming%20and%20I%20would%20like%20it%20but%2C%20in%20the%20event%2C%20what%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3B%26nbsp%3Bdelivered%20was%20far%20superior%20(I%20no%20longer%20felt%20like%20a%20lone%20voice%20in%20the%20wilderness%20since%20his%20team%20were%20clearly%20ahead%20of%20me).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20diversity%20of%20solution%20approaches%20now%20possible%20within%20Excel%20as%20a%20single%20software%20system%20is%20staggering.%26nbsp%3B%20But%20then%2C%20if%20one%20looks%20at%20the%20VBA%20side%20of%20the%20house%2C%20solutions%20vary%20from%20basic%20macro%20recorder%20(with%20its%20multiplicity%20of%20'selects')%20through%20to%20centralised%20error%20trapping%20(such%20as%20that%20offered%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F234290%22%20target%3D%22_blank%22%3E%40Craig%20Hatmaker%3C%2FA%3E%26nbsp%3Bor%26nbsp%3BBovey%20%3CEM%3Eet%20al%3C%2FEM%3E)%20combined%20with%20Classes%20and%20associated%20methods.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2016500%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2016500%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%3EOur%20'philosophical%20discussion%20may%20be%20distracting%20somewhat%20from%20your%20post'.%26nbsp%3B%20Please%20accept%20my%20apologies.%3C%2FP%3E%3CP%3EWould%20it%20be%20a%20good%20idea%20to%20post%20a%20sequence%20of%20comments%20drawing%20attention%20to%20the%20techniques%20you%20have%20used%20to%20achieve%20the%20result.%26nbsp%3B%20I%20suspect%20that%20not%20all%20of%20the%201500%2B%20views%20will%20be%20followed%20up%20by%20file%20downloads%20and%20not%20all%20the%20readers%20will%20be%20confident%20of%20picking%20out%20the%20key%20points.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20starting%20point%20might%20be%20the%20role%20of%20SUMIFS%20in%20creating%20the%20array%20of%20'bottom%20line'%20values.%26nbsp%3B%20Since%20the%20array%20is%20going%20to%20be%20evaluated%20eight%20times%20it%20needs%20to%20be%20compact%20and%20the%20use%20of%20wildcard%20matches%20reduces%20the%20character%20count.%26nbsp%3B%20At%20the%20moment%2C%20I%20think%20MMULT%20is%20the%20only%20Excel%20function%20that%20is%20capable%20of%20processing%20a%202D%20array%20row%20by%20row%20but%20it%20is%20highly%20mathematical%20and%20may%20warrant%20explanation.%3C%2FP%3E%3CP%3E%5B%20In%20my%20professional%20career%2C%20I%20probably%20used%20Matrix%20equations%20more%20often%20than%20written%20English%20language%20but%20that%20is%20far%20from%20normal!%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2019951%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2019951%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%3EGreat%20solutions%20-%20and%20very%20interesting%20to%20see%20them%20-%20thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(I%20thought%20the%20challenge%20was%20supposed%20to%20avoid%20using%20CSE%20curly%20bracket%20%7Barrays%7D%3F%3F)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2020056%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2020056%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%3EYou%20are%20correct.%26nbsp%3B%20I%20had%20key%20points%20in%20mind%2C%20but%20the%20detailed%20treatment%20serves%20that%20purpose%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2021666%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2021666%22%20slang%3D%22en-US%22%3EGo%20ahead.%20By%20all%20means%2C%20I'd%20love%20a%20ninth!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2022823%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2022823%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3C%2FP%3E%3CP%3EAs%20with%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's%20challenge%2C%20I%20have%20addressed%20the%20problem%2C%20but%20not%20the%20challenge%20which%20requires%20the%20use%20of%20specific%20techniques.%20Also%2C%20I%20have%20made%20no%20attempt%20to%20minimise%20the%20character%20count.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20typesAbbr%2C%20%7B%22S*%22%2C%22C*%22%2C%22A*%22%2C%22O*%22%7D%2C%0A%20%20branchNum%2C%20SEQUENCE(9)%2C%0A%20%20crosstab%2C%20SUMIFS(Amounts%2C%20Types%2C%20typesAbbr%2C%20Branches%2C%20branchNum)%2C%0A%20%20OperatingProfit%2C%20IF(%7B0%2C1%7D%2C%20MMULT(crosstab%2C%20SignCashFlow)%2C%20branchNum)%2C%0A%20%20k%2C%20%7B1%3B2%3B3%3B4%3B5%7D%2C%0A%20%20ordered%2C%20SORT(OperatingProfit%2C2%2C-1)%2C%0A%20%20INDEX(ordered%2C%20k%2C%20%7B1%2C2%7D)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%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%20207px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243487i6C9CCBD41B80F698%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2022898%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2022898%22%20slang%3D%22en-US%22%3E%3CP%3ECongrats%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-%20you've%20also%20achieved%20what%20was%20going%20to%20be%20my%20challenge%20%2310%20-%20slotting%20the%20branch%20numbers%20in%20alongside%20the%20profit%20results!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%20any%20challenge%20like%20this%20becomes%20an%20artificial%20puzzle%20(to%20see%20what%20you%20can%20do%20if%20you%20limit%20yourself%20making%20use%20of%20the%20new%20dynamic%20array%20functions)%20when%2C%20in%20real%20life%2C%20you%20could%20often%20get%20there%20more%20simply%20if%20you%20were%20prepared%20to%20use%20an%20extra%20column%20or%20row%20and%20fill.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20the%20puzzle%20does%20become%20a%20notch%20more%20challenging%20if%20you%20refuse%20to%20allow%20yourself%20the%20ability%20to%20use%20any%20curly%20brackets%20%7B%7D%20at%20all%2C%20and%20also%20look%20to%20take%20yourself%20through%20a%20process%20that%20reduces%20down%20the%20number%20of%20functions%20used%20(which%20has%20the%20side%20impact%20of%20reducing%20character%20count).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%20done!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2005412%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2005412%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%3ESet%20of%20solutions%20attached%20-%20they%20work%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%231%20192%20characters%3C%2FP%3E%3CP%3E%3DSUM(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1))%3CBR%20%2F%3E-2*(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%232%20198%20characters%3C%2FP%3E%3CP%3E%3DSUM((%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1))%3CBR%20%2F%3E-2*(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E)%26gt%3B0)*1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%233%20200%20characters%3C%2FP%3E%3CP%3E%3DSUM(((%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1))%3CBR%20%2F%3E-2*(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CSEQUENCE(1%2C9%2C1%2C1)%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E)%26lt%3B0)*1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%234%20264%20characters%3C%2FP%3E%3CP%3E%3DLET(%3CBR%20%2F%3EBranchList%2C%3CBR%20%2F%3ESEQUENCE(1%2C9%2C1%2C1)%2C%3CBR%20%2F%3EProfitArray%2C%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList)%3CBR%20%2F%3E-2*(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E)%2C%3CBR%20%2F%3ESUM((ProfitArray%3DMAX(ProfitArray))*BranchList)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%235%20264%20characters%3C%2FP%3E%3CP%3E%3DLET(%3CBR%20%2F%3EBranchList%2C%3CBR%20%2F%3ESEQUENCE(1%2C9%2C1%2C1)%2C%3CBR%20%2F%3EProfitArray%2C%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList)%3CBR%20%2F%3E-2*(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E)%2C%3CBR%20%2F%3ESUM((ProfitArray%3DMIN(ProfitArray))*BranchList)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%236%20257%20characters%3C%2FP%3E%3CP%3E%3DLET(%3CBR%20%2F%3EBranchList%2C%3CBR%20%2F%3ESEQUENCE(1%2C9%2C1%2C1)%2C%3CBR%20%2F%3EProfitArray%2C%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList)%3CBR%20%2F%3E-2*(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E)%2C%3CBR%20%2F%3ESUM(MAX(ProfitArray)%2C-MIN(ProfitArray))%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%237%20304%20characters%3C%2FP%3E%3CP%3E%3DLET(%3CBR%20%2F%3EBranchList%2C%3CBR%20%2F%3ESEQUENCE(1%2C9%2C1%2C1)%2C%3CBR%20%2F%3EProfitArray%2C%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList)%3CBR%20%2F%3E-2*(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E)%2C%3CBR%20%2F%3ESUM((ProfitArray%3DMIN(IF((ProfitArray%26lt%3B0)%2C%22%20%22%2C(ProfitArray%26gt%3B0)*ProfitArray)))*BranchList)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%238%20304%20characters%3C%2FP%3E%3CP%3E%3DLET(%3CBR%20%2F%3EBranchList%2C%3CBR%20%2F%3ESEQUENCE(1%2C9%2C1%2C1)%2C%3CBR%20%2F%3EProfitArray%2C%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList)%3CBR%20%2F%3E-2*(%3CBR%20%2F%3ESUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Cost%20of%20Sales%22)%3CBR%20%2F%3E%2BSUMIFS(Amounts%2CBranches%2CBranchList%2CTypes%2C%22Operating%20expenses%22)%3CBR%20%2F%3E)%2C%3CBR%20%2F%3ESUM((ProfitArray%3DMAX(IF((ProfitArray%26gt%3B0)%2C%22%20%22%2C(ProfitArray%26lt%3B0)*ProfitArray)))*BranchList)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%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-2022954%22%20slang%3D%22en-US%22%3ERe%3A%20Profit%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2022954%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%3EWhat's%20the%20difference%20between%20array%20constant%20and%20non-array%20constant%3F%20Constant%20is%20constant.%20It's%20hardcoded%20into%20the%20formulae%20or%20defined%20in%20grid%2C%20IMHO%20that's%20the%20only%20point.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Trusted Contributor

If you believe you can comfortably manipulate arrays, then these eight formula challenges will test your mettle! I superbly love the sixth. 

Twifoo_0-1608827170232.png

 

79 Replies

@Twifoo@Twifoo

The thing that might have defeated me is the spirit of 'No CSE'.  About 5 years ago I had schooled myself to use CSE for almost all formulas in order to avoid filled formulas and relative referencing.  

I also developed the technique of using named formulas to avoid the dreaded implicit intersection and ensure calculations were performed correctly as arrays.

 

Now it doesn't matter since I am using Excel 365.  My formulas would not meet the criteria but ...

 

 

 

= LET(
  crosstab, SUMIFS(Amounts,Types,listTypes,Branches,HdrBranches),
  OperatingProfit, MMULT(TRANSPOSE(SignCashFlow), crosstab),
  ProfitableBranches, COUNT( IF(OperatingProfit>0, 1)),
  LossmakingBranches, COUNT(IF(OperatingProfit<0, 1)),
  MinProfit, MIN(OperatingProfit),
  MaxProfit, MAX(OperatingProfit),
  BestPerforming, XLOOKUP(MaxProfit,OperatingProfit,HdrBranches),
  WorstPerforming,XLOOKUP(MinProfit,OperatingProfit,HdrBranches),
  CHOOSE({1;2;3;4;5;6},
    SUM(OperatingProfit),
    ProfitableBranches,
    LossmakingBranches,
    BestPerforming,
    WorstPerforming,
    MaxProfit - MinProfit) )

 

 

 

The formula avoids the need to repeat the SUMIFS step by retuning all six results as a single array.

@Peter Bartholomew , in opposite I schooled myself to avoid CSE where possible. But by another formulas. Simplest cases SUMPRODUCT() instead of SUM(), MATCH/INDEX instead of MATCH, etc. Yes, I know about the names.

@Sergei Baklan 

A far more normal strategy I agree.  On the other hand SUMPRODUCT and, I think, AGGREGATE are array formulas, just no CSE.  It is mainly the user hostility to the presentation of CSE formulas that makes them viewed as methods of last resort - too clever by half!

 

Logically Excel should prevent a blank line from being inserted between a formula and any of its relative reference because the meaning is ambiguous, but that doesn't happen because in standard Excel practice the risk of error is less important that the fluency of the action-led user interface.  Instead, it is the array formula that blocks the insertion of rows, despite there being no such ambiguity.  I seem to remember a message such as 'you cannot change part of an array formula'.  Since I cannot insert a blank cell into the middle of an array wouldn't it be more logical to assume I do not wish to and simple repaint the array unaltered just as a DA does now (an extra #N/A! at the end if must be).

 

It was an uphill grind to switch to array formulas but now, thankfully, the position is now reversed.  If I were forced to revert to 'enter the formula in cell $PQ17 and fill down 1027 cells', I think I would just delete Excel and use a pocket calculator

@Peter Bartholomew 

Yes, arrays were all the time, just before in back-end and you shall to know what is behind and which formulas to use, now they are on front-end. Hope that's only the first, but significant one, step and we will be able to manipulate not only with entire array as an object, but with each element of it. 

 

By the way, I see nothing dramatic in Fill Down, doesn't matter on couple of cells or on couple thousand of cells.

@Sergei Baklan 

I could amplify why I feel single cell relative referencing is a slap-dash approach, though in keeping with the idea of end-user computing.  That would take the discussion too far from @Twifoo 's challenge though, and I would like to see what others come up with.

@Peter Bartholomew and @Sergei Baklan, I edited my post to include two additional challenges, thereby bringing the total to eight. Please see the revised version of the file attached thereto. 

@Twifoo 

I confess, I have never seen or attempted a formula this large in Excel.

 

= LET(
   crosstab, SUMIFS(Amounts, Types, listTypes, Branches, HdrBranches),
   OperatingProfit, MMULT( TRANSPOSE(SignCashFlow), crosstab ),
   ProfitableBranches, COUNT( IF(OperatingProfit > 0, 1) ),
   LossmakingBranches, COUNT( IF(OperatingProfit <0, 1) ),
   MaxProfit, MAX(OperatingProfit),
   BestPerforming, XLOOKUP( MaxProfit, OperatingProfit, HdrBranches ),
   MaxLoss, MIN(OperatingProfit),
   WorstPerforming, XLOOKUP( MaxLoss, OperatingProfit, HdrBranches ),
   MinGain, MIN(IF(OperatingProfit>0, OperatingProfit) ),
   MinGainBranch, XLOOKUP( MinGain, OperatingProfit, HdrBranches ),
   MinLoss, MAX(IF(OperatingProfit<0, OperatingProfit) ),
   MinLossBranch, XLOOKUP( MinLoss, OperatingProfit, HdrBranches ),
   CHOOSE({1;2;3;4;5;6;7;8},
      SUM(OperatingProfit),
      ProfitableBranches,
      LossmakingBranches,
      BestPerforming,
      WorstPerforming,
      MinGainBranch,
      MinLossBranch,
      MaxProfit-MaxLoss ) )

 

Would it be better to break the calculation into parts even though it would mean recalculating the SUMIFS calculation within formula? I could, for example, have produced an array of MAX/MINs and then performed an XLOOKUP on the array.

Alternatively, should I have nested the MAX/MINs within the XLOOKUP to reduce the count of LET parameters?

 

 

 

@Peter Bartholomew, me too! But would you believe me if I tell you each of my formulas did not exceed 200 characters?

@Twifoo 

I have faith in you, so yes I am prepared to believe the 200 characters.  I would also agree that conciseness is good, but it is not the only metric of relevance.  Comparing solutions will always have an element of subjectivity because the secondary objectives can be so different (getting the answer right is not so controversial).

 

Clearly my selection of names such as 'OperatingProfit' and 'LossmakingBranches' does not serve the cause of conciseness too well!  On the other hand, I did consider the number of floating point operations implicit in the formulas and it was that which led me to the unusual strategy of combining the 8 distinct calculations into a single formula.

 

My formula appears to be 950 characters (including the 140 spaces used to improve layout).  That is not too bad when one considers that it delivers all 8 calculations.

 

I will be interested to see your solution when you are ready to communicate it.  Did the 'no CSE' rule impact you significantly?

@Peter Bartholomew, I agree with you that the choice of formulas is somehow a matter of personal preference, hence usually subjective. Like @Sergei Baklan, I schooled myself to exploit the powers of functions that can natively process array operations.

@Twifoo 

I hope my response has not put others off contributing traditional spreadsheet solutions!

The solutions I am exploring are not available within most Excel versions and would not suit many users.

 

In the new modification to my solution, I have made it more targeted and eliminated a number

of intermediate variables.  The image below shows the spilt range output from a single formula

 

image.png

 

= LET(
  Crosstab, SUMIFS(Amounts, Types, listTypes, Branches, HdrBranches),
  OperatingProfit, MMULT( TRANSPOSE(SignCashFlow), Crosstab ),
  Profits, IF(OperatingProfit > 0, OperatingProfit),
  Losses, IF(OperatingProfit<= 0, -OperatingProfit),

   KeyMetrics,HSTACK("", 
     SUM(OperatingProfit), COUNT(Profits), COUNT(Losses), MAX(Profits)+MAX(Losses)),
   ExtremeValues, HSTACK("", MAX(Profits), MIN(Profits), -MIN(Losses), -MAX(Losses)),
   AssociatedBranches, XLOOKUP( ExtremeValues, OperatingProfit, HdrBranches),
   VSTACK("", Headings1, KeyMetrics, Headings2, AssociatedBranches, ExtremeValues) )

I have chosen to show formulas using Charles Williams's HSTACK and VSTACK functions because the intent is clearer, but CHOOSE with the correctly oriented index arrays will achieve the same result.

 

 

@Peter Bartholomew 

Although you indirectly admitted that your solution is inherently inapplicable to most Excel users, it nonetheless behooves me to commend you for your tenacious pursuit thereof! Before I ultimately divulge my solutions, I deem it prudent to meanwhile anticipate possible solutions from @Sergei Baklan, and other Excel Experts from this Community. 

@Twifoo , sorry, perhaps some later, bit overloaded with current project and answer here only to have some rest and if that takes not more than 5 minutes. Your task looks quite interesting, but I'm very far from accounting world and for me to understand what "Combined bottom line of All Branches" means will take significant time.

 

@Peter Bartholomew  solutions looks interesting, but as I avoided CSE I try to avoid any macros and third-party add-ins. Charles suggests great additions, however I'd prefer to wait till the same will be out of the box.

 

@Sergei Baklan 

Don't worry, I empathize with you. Bottom Line simple means the final amount, which refers to the values in the row for Operating Profit. Thus, Combined Bottom Line of All Branches refers to the value for Total Operating Profit, which is 45,800.00. I trust that I have somehow clarified your doubts as to the terms used in this formula challenge. By the way, Accounting is the language of business! It is only through Accounting that business information can be communicated to the users thereof. 

@Sergei Baklan 

Like you, I avoid add-ins especially when sharing ideas with others.   @Charles Williams  work I have enjoyed, though, and I hope some of his ideas eventually get adopted by Microsoft.  I have found the array manipulation offered in the form of slicing and stacking, ACCUMULATE for accountancy corkscrew problems and some of the Regular Expression formulas to be particularly effective.  I did include a working version of the present formula based on CHOOSE, because people tend to be unimpressed when they open a workbook that refuses to calculate.

= LET(
   Crosstab, SUMIFS(Amounts, Types, listTypes, Branches, HdrBranches),
   OperatingProfit, MMULT( TRANSPOSE(SignCashFlow), Crosstab ),
   Profits, IF(OperatingProfit > 0, OperatingProfit),
   Losses, IF(OperatingProfit<= 0, -OperatingProfit),

   KeyMetrics, CHOOSE({1,2,3,4}, SUM(OperatingProfit), COUNT(Profits), COUNT(Losses), MAX(Profits)+MAX(Losses)),
   ExtremeValues, CHOOSE({1,2,3,4}, MAX(Profits), MIN(Profits), - MIN(Losses), -MAX(Losses)),
   AssociatedBranches, XLOOKUP( ExtremeValues, OperatingProfit, HdrBranches),
   CHOOSE({1;2;3;4;5}, Headings1, KeyMetrics, Headings2, AssociatedBranches, ExtremeValues) )

 

@Peter Bartholomew 

So, we are almost in sync.

@Twifoo , thank you for the clarification. Yes, sure , accounting is the language of business, but English terms are not always understandable if your business uses another language. 

@Twifoo 

1 =SUM(F8:N8)

2 =SUM((F8:N8>0)*1)

3 =SUM((F8:N8<0)*1)

4 =SUM((F8:N8=MAX(F8:N8))*F1:N1)

5 =SUM((F8:N8=MIN(F8:N8))*F1:N1)

6 =SUM(MAX(F8:N8),-MIN(F8:N8))

7 =SUM((F8:N8=MIN(IF((F8:N8<0)," ",(F8:N8>0)*F8:N8)))*F1:N1)

8 =SUM((F8:N8=MAX(IF((F8:N8>0)," ",(F8:N8<0)*F8:N8)))*F1:N1)

keenadvice_0-1607628066170.png

Posted by https://www.linkedin.com/in/excel-guy/ 

Kindly follow Rule 1.