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.
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?
PeterBartholomew1, me too! But would you believe me if I tell you each of my formulas did not exceed 200 characters?
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?
I'm so sorry. I lied! All my formulas are less than 130 characters!
It always amazes me how much can be achieved by traditional Excel in the hands of a master! Given how different my objectives are from those assumed by your challenge it is perhaps surprising that there is as much commonality in our approaches as appears the case. You calculate the basic matrix as the transpose of that printed as a table but maybe that fits the MMULT formulas better. The main difference in the selection of functions appears to be your use of FREQUENCY. I tend to reserve it for stats calculations but it appears to have much wider application.
As an Excel 365 user, SUMPRODUCT as an array wrapper is history and I have no way of testing which functions shared its magic properties (AGGREGATE for one?). I think we are reaching the point at which 365 is no longer best regarded as spreadsheet software, more an interactive programming environment that just happens to be capable of running legacy spreadsheets.
If only I had LAMBDA, it is starting to be annoying!
PeterBartholomew1, I agree with you that the choice of formulas is somehow a matter of personal preference, hence usually subjective. Like SergeiBaklan, I schooled myself to exploit the powers of functions that can natively process array operations.