SOLVED

Profit Formula Challenge

Silver 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.
1 best response

Accepted Solutions
best response confirmed by Twifoo (Silver Contributor)
Solution

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

View solution in original post