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.
