Forum Discussion

Twifoo's avatar
Twifoo
Silver Contributor
Oct 17, 2020
Solved

Profit Formula Challenge

If you believe you can comfortably manipulate arrays, then these eight formula challenges will test your mettle! I superbly love the sixth.   
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Dec 31, 2020

    keenadvice 

    Don't forget that Twifoo intended the challenge to be an examination of the use of advanced array methods available within traditional Excel.  The dislike of CSE is understandable but is simply a by-product of the user-interface design. 

     

    As I noted earlier, I had trained myself to commit all formulas with CSE but I would have preferred not to need it.  For some reason my proposal to reverse the conventions, that is to use CSE to introduce the additional implicit intersection step and use Enter to allow the calculation to proceed as a simple array operation was not well-received 🙂

     

    I share the views of SergeiBaklan and Twifoo in that I see no need to avoid {1,-1,1,-1} as an array constant on the grounds that the notation is similar to the CSE notation.  The array constant is an important element of array calculation.  They need to be used with care, not because they are arrays, but because they are constants.  If there exists a possibility that they may change, a named variable would be more appropriate than a constant embedded within a formula.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 19, 2020

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

 

 

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

 

 

Resources