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.
Expected result for challenge #9 is
28,200
20,500
9,700
7,900
3,600
Expected result for challenge #10 includes the branch number on the left hand column
4 28,200
6 20,500
1 9,700
7 7,900
5 3,600
If you really want to test your mastery of dynamic arrays, I think that completely eliminating the (now old, 'hard') curly bracket arrays becomes a truer challenge. You can either do it, or not.
But if you stop to think about it for a second, completely eliminating {} should not be at all difficult. Something like ={1,-1,1,-1} in 'old money' just becomes something like =CHOOSE(SEQUENCE(1,4),1,-1,1,-1) in 'new money' - maybe highlighting the everyday usefulness of the new dynamc array sequence function?
Maybe try both ways, with and without {}? If only to demonstrate to the world your belief that {} will enhance efficiency, reduce formula length and continue to persist in the new dynamic array world?
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.
Hi Twifoo
Do you have a solution for #9 and #10? I am dying to see yours. At the same time I am keen to post the solution I worked up and compare (although it may not be as neat as yours, having given myself the extra constraint of no {}). We can always learn from each other - that's the beauty of these things.
Based on the other challenges the correct form seems to be: I'll show you mine if you show me yours.
Do you have a solution?
I'm interested to see your solution Twifoo.
For challenge #9 (list of top 5 performing branches listed in order of profitablity) I've got it down to 173 characters, avoiding {} and, at the same time, demonstrating the potential value of the new dynamic array functions Let, Sequence, Switch and Sort.
I'll be interested in your result (and yes, you do make it much easier on yourself if you use {} as a shortcut to force your embedded functions to run, just leaving the outer 'shell' as the dynamic array - I appreciate you're not up for that particular challenge!!).
- Well explained, PeterBartholomew1 . Array constants must not be avoided, but must be used with care, not because they are arrays, but because they are constants!
