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.
Our 'philosophical discussion may be distracting somewhat from your post'. Please accept my apologies.
Would it be a good idea to post a sequence of comments drawing attention to the techniques you have used to achieve the result. I suspect that not all of the 1500+ views will be followed up by file downloads and not all the readers will be confident of picking out the key points.
The starting point might be the role of SUMIFS in creating the array of 'bottom line' values. Since the array is going to be evaluated eight times it needs to be compact and the use of wildcard matches reduces the character count. At the moment, I think MMULT is the only Excel function that is capable of processing a 2D array row by row but it is highly mathematical and may warrant explanation.
[ In my professional career, I probably used Matrix equations more often than written English language but that is far from normal!]
Your apologies are hereby accepted. Contrariwise, written English language is my main course while matrix equations rarely serve as my dessert.
Nevertheless, I presume that you might have overlooked the link to my detailed explanations. If so, kindly do read from here.
You are correct. I had key points in mind, but the detailed treatment serves that purpose as well.
- After reading my explanations, kindly inform me of your thoughts thereon.
About a year ago I backed out of writing an article for Jeff Robson advocating the use of dynamic array methods for financial models, on the grounds that matrix multiplication would be unacceptable to the financial modelling community as a means of evaluating corkscrews or column sums. Perhaps I gave up too easily, though now I hope LAMBDA functions will offer clearer syntax.
For me, the 'operating profit' features in so many of your formulas that it is crying out to be a named formula. Conciseness then becomes less of an issue. That said, I admire to way in which you have navigated your way through the pitfalls scattered though the problem. I was never keen on the formula
=ROW(1:9)
because it creates a false link to actual spreadsheet rows (inserting a new row invalidates some of the formulae) but it is concise and one uses the tools one has to hand. I didn't think of using the FREQUENCY function to count the distinct financial outturns but the use of INDEX to pick out a specific term is neat, These days, I only develop for MS365 (I should be so lucky to be able to pick and choose). Even then, your challenge provided interest (I appreciate that, as usual, I ignored all rules and constraints) but your approach is relevant even in the context of MS365.
It is interesting that the discussion has so many views but so few proposed solutions. I suspect that does say something about the level of familiarity that exists within the financial modelling community regarding MMULT (lucky you didn't need MINVERSE)
Footnote: I did once try to demystify MMULT
https://youtu.be/sHkXZZz8ANs (about 8 min)