Forum Discussion
Profit Formula Challenge
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.
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.
PeterBartholomew1 , 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.
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 🙂
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.
