10-17-2020 12:26 AM - edited 10-17-2020 02:53 PM
If you believe you can comfortably manipulate arrays, then these eight formula challenges will test your mettle! I superbly love the sixth.
10-17-2020 05:56 AM - edited 10-17-2020 08:13 AM
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.
10-17-2020 07:23 AM
@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.
10-17-2020 08:00 AM
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
10-17-2020 08:25 AM
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.
10-17-2020 11:34 AM - edited 10-17-2020 11:34 AM
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.
10-17-2020 02:56 PM
@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.
10-17-2020 11:39 PM
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?
10-18-2020 03:30 AM
@Peter Bartholomew, me too! But would you believe me if I tell you each of my formulas did not exceed 200 characters?
10-18-2020 04:54 AM
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?
10-18-2020 06:15 AM
@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.
10-19-2020 02:45 AM
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.
10-19-2020 08:03 AM
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.
10-19-2020 10:15 AM
@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.
10-19-2020 10:52 AM - edited 10-19-2020 10:55 AM
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.
10-21-2020 12:59 PM
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) )
10-22-2020 02:11 AM
@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.