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.
Twifoo Hi
Indeed, it is a wonderful initiative that is full of learning. I appreciate your Profit Challenge Post. Under the given criteria I could hardly do the first challenge.
=SUMPRODUCT((Amounts)*(Types={"Sales Revenues","Additional Revenues"}))-SUMPRODUCT((Amounts)*(Types={"Cost of Sales","Operating Expenses"}))
Eagerly waiting for the solution.
Thanks
Tauqeer
- I will eventually divulge my solutions soon!
After some more fun time on this (and getting behind the rest of the family on re-runs of Lost on TV)
I've found that you can replace the core piece of the formula above
So instead of using this for the "ProfitArray" above, at 186 characters:
=SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1))
-2*(
SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Cost of Sales")
+SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Operating expenses")
)You can get the character count of the core piece of the formula down to 131:
=MMULT(
TRANSPOSE(
IF(Branches=SEQUENCE(1,9,1,1),1,0)*
(1+
(Types="Cost of Sales")*-2
+(Types="Operating Expenses")*-2
)),
Amounts)
