Profit Formula Challenge

[Twifoo]

If you believe you can comfortably manipulate arrays, then these eight formula challenges will test your mettle! I superbly love the sixth. 

[#2]

@Sergei Baklan 

<I have =SUM(A1:A10).  To modify =SUM(A1:A11) I simply drag the range on one cell>

I am not sure you have made the point!  I agree completely that the modification is quick and easy ('cheap and cheerful' is another English expression that may apply).

It is also highly prone to error; I might forget to make the change.  Also, there is nothing to indicate how big the range should be (Rogoff and Reinhart).  I would argue that it is a recipe for error and, possibly, a significant contributor to the fact that fewer than one in ten spreadsheets are sufficiently free of error so as to be usable.

[#3]

@Twifoo 

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!]

[#4]

@Peter Bartholomew 

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.

[#5]

@Twifoo 

Great solutions - and very interesting to see them - thank you!

(I thought the challenge was supposed to avoid using CSE curly bracket {arrays}??)

[#6]

@keenadvice 

I'm glad you found my solutions interesting! All of my formulas do not require Ctrl+Shift+Enter, regardless of the Excel version of the user. The curly brackets you see enclose the array constants.

[#7]

@Twifoo 

You are correct.  I had key points in mind, but the detailed treatment serves that purpose as well.

[#8]

After reading my explanations, kindly inform me of your thoughts thereon.

[#9]

@Twifoo 

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)

[#10]

@Twifoo can I issue a challenge to you (and other followers of this thread)??

It's in the vein of your other challenges

It's very easy to describe

But I am having great difficulty compressing the character count in the solution

I strongly suspect you will have something to offer (if you dare rise to a challenge yourself!)

@Twifoo are you brave enough to accept a #9 easy-to-describe challenge?

[#11]

Go ahead. By all means, I'd love a ninth!

[#12]

I share your suspicion that a vast majority, including those who profess to be advanced Excel users, are unfamiliar with the power of MMULT. For transparency, my formula challenge was not about modern Excel; it was about advanced Excel.

[#13]

@Twifoo 

<it was about advanced Excel>

I realised that.  Something that was of interest to me was how close it brought your solution to methods that I had adopted before I paid out for an additional 365 license.  About the only difference was the fact that I used defined names to hold meaningful fragments of formula and provide a description of the formula result.

All the LET function gave me was an opportunity to lay the formula out as a sequence of steps rather than a nested structure and the ability to aggregate the source data once and use it many times (a helper range would achieve the same objective and was frequently advocated by @Charles Williams as a device to speed up Excel calculation).

[#14]

@Peter Bartholomew 

I apologise for the second link to the video clip.  It was still in the paste buffer from last night and I don't seem to have an opportunity to edit it out of the message.

I am curious to see what challenge part #9 might involve.

[#15]

Me too. Let's wait the details of Challenge 9 that @keenadvice will present.

[#16]

@Twifoo 

OK here it is - challenge #9 - so glad you're up for it!

It's very easy to describe - 'simply' list out the profits from the 5 top-performing branches

So the answer, of course, looks like this:

28,200

20,500

9,700

7,900

3,600

The trick is to complete the challenge complying with @twifoo's original constraints (using the original lists of data in columns A, B & C, making use of native Excel 365 functionality, with the result produced by a formula entered into one cell and without ever pressing "Ctrl shift enter").  Plus, to make it even more fun, this time there are no 'forced' array formulas allowed in the result at all (i.e no curly brackets allowed at all).

I have struggled to get the result down to less than 200 characters - and I'm not seeing it as perfect, so I expect there is room for improvement.

Good luck!

(PS - to achieve my result, I have had to use a handful of the Excel functions released in the last year, so I think it becomes a real test of your grasp of the new advances)

[#17]

Do you mean that those five answers will be entered in five cells but using the same formula, copied down to the next four cells? My challenges required only one answer entered in one cell.

[#18]

@keenadvice 

As with @Twifoo's challenge, I have addressed the problem, but not the challenge which requires the use of specific techniques. Also, I have made no attempt to minimise the character count.

= LET(
  typesAbbr, {"S*","C*","A*","O*"},
  branchNum, SEQUENCE(9),
  crosstab, SUMIFS(Amounts, Types, typesAbbr, Branches, branchNum),
  OperatingProfit, IF({0,1}, MMULT(crosstab, SignCashFlow), branchNum),
  k, {1;2;3;4;5},
  ordered, SORT(OperatingProfit,2,-1),
  INDEX(ordered, k, {1,2}) )

[#19]

@Twifoo 

I mean one answer entered in one cell only

[#20]

Congrats @Peter Bartholomew - you've also achieved what was going to be my challenge #10 - slotting the branch numbers in alongside the profit results!

Of course any challenge like this becomes an artificial puzzle (to see what you can do if you limit yourself making use of the new dynamic array functions) when, in real life, you could often get there more simply if you were prepared to use an extra column or row and fill.

I think the puzzle does become a notch more challenging if you refuse to allow yourself the ability to use any curly brackets {} at all, and also look to take yourself through a process that reduces down the number of functions used (which has the side impact of reducing character count).

Well done!

[#21]

What's the expected result? I refuse to avoid the use of array constants because doing so will impose an additional rule, which I consider as more appropriate for a separate discussion, not here.