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]

@Twifoo 

I share your suspicion that a vast majority, including those who profess to be advanced Excel users, are unfamiliar with the power of MMULT.

With dynamic arrays MMULT becomes standard operation. Even if you didn't use it before it's hard to survive without it with DA. Of course, depends on content, but to aggregate arrays it's most suitable.

[#3]

@Twifoo 

What's the difference between array constant and non-array constant? Constant is constant. It's hardcoded into the formulae or defined in grid, IMHO that's the only point.

[#4]

@Twifoo 

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?

[#5 · Accepted solution]

@keenadvice 

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 @Sergei Baklan 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.

[#6]

@keenadvice 

I still refuse to avoid the use of curly brackets! They house the wildcards I used in my formulas.

[#7]

Well explained, @Peter Bartholomew . Array constants must not be avoided, but must be used with care, not because they are arrays, but because they are constants!

[#8]

@Twifoo I have been one of those following this thread with some interest having enjoyed these kinds of challenges in the past and I concur that an array constant should be preferred to a CHOOSE construction unless of course some of the elements may be variable. Perhaps one of the first instances of a formula similar to your proposed solution was here . Now, in the latest version, XLOOKUP may be used in place of that LOOKUP/FREQUENCY combination.

@Sergei Baklan With dynamic arrays MMULT becomes standard operation
Indeed Dynamic arrays extend Excel to operate like other array programming languages with operations such as lifting and broadcasting (but without advanced concepts like multidimensional arrays and tensor products). In this respect MMULT is a fairly straight forward operation requiring no more than high school-level mathematics.

[#9]

You're absolutely right! I certainly learned plenty of techniques from your astounding insights therein before posting a formula challenge like those even crossed my mind. I recommend readers of this thread to study your contributions therein just like I did.

[#10]

@lori_m 

I didn't say MMULT is complex and require extra skills, I only tried to say it didn't use so widely before DA.

[#11]

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!!).

[#12]

@Twifoo I got my solution for challenge #9 down to 173 characters

But yes, lost any ability to use * wildcards (makes it a fraction longer but also clearer I feel)

I'd be interested to see your solution

[#13]

@lori_m what should probably be preferred, in all situations, is this

https://bit.ly/3mWR39e

With @Twifoo's brain-stretching challenge we are operating in a rareified and artificial atomosphere today, where financial modelling's most basic 'rule of thumb' has gone out the window anyway

[#14]

@lori_m 

Hi Lori

<MMULT is a fairly straightforward operation requiring no more than high school mathematics>

I think you may be overstating the simplicity of matrix multiplication.  I seem to remember encountering matrix theory in the first year at uni.  Mind you, it is possibly incorrect to equate the complexity of a subject with ones age when first encountering it.  I also was introduced to set theory at university; now it is taught at kindergarten! 

I followed your link back to the XOR LX site.  There again, I would have cheated and gone for the geometric mean of the annual growth

=(Y[2013]/Y[2010])^(1/3)-1

and, in so doing, missed out on all the cunning Excel tricks. 

=INDEX(SORT(IF({1,0},Y[2013]/Y[2010],Y[Region]),,-1),1,2)

Of course, using structured references would have got me drummed out of the Brownies, despite the fact that the formula is marginally longer that with direct references.

One thing that modern excel provides is the SORT function which brings the treasure to the surface.

Now with the LAMBDA function, you should be able to roll-your-own multidimensional arrays such as 

UNITS(region, sector, timeperiod)

where the UNITS refers to a LAMBA to access the data column 'Memory'

= LAMBDA(k₁, k₂, k₃,
    LET(
      p, n₂*n₃*k₁ + n₃*k₂ + k₃ + 1,
      V, INDEX(Memory, p),
    V) )

The next challenge would be a further function to sum over a selected index or, more advanced to bring varying sales prices/region into the summation.

[#15]

@keenadvice 

I would gladly chuck the (FAST) 'rule of thumb' out of the window.  It is an irrelevant measure, since it captures neither complexity nor transparency with any precision.  I found that I would completely reverse about 25% of the FAST standard to achieve what I consider better practice, starting with 'Do NOT create calculation blocks, always reference data at source.  On a more positive note, I was happy to go along with about 75% of the standard!

[#16]

@Sergei Baklan  

Agree that it's not much of a stretch to apply MMULT once the initial barrier of dynamic arrays is overcome. The main issue in my experience is that Excel is not consistent over function input types which can lead to confusion over what to expect functions to return. For example one might expect the N function with a range as input to spill when it actually needs to be converted to an array as in the formula at the bottom of this post.

@Peter Bartholomew 

"In this respect" was omitted from your quote which places that statement in a context relative to other array operations such as broadcasting and lifting - see comment above. Matrices (and sets) can be studied at different levels, as you say, but the basic rules of computation as studied in high schools these days should be sufficient here (not the representation as a general linear transformation as covered in uni courses). Obviously familiarity with techniques of application only comes with practice though now with the introduction of LAMBDA implementation can become standardised rendering technical details less of an issue.

Take as an example a function that returns an array of column sums (allowing for blanks or text in the data range like SUM formulas do). The named formula below may appear tricky but if one gets used to just entering =SUMCOLS(range) in any given cell one can treat just as any other built-in function,

SUMCOLS:=LAMBDA(matrix,MMULT(1^TRANSPOSE(ROW(matrix)),N(+matrix)))

[#17]

@lori_m 

Neat.  I tend to specialise in the longwinded and prosaic!

= LAMBDA(matrix,
    LET(
      n, COLUMNS(matrix),
      k, SEQUENCE(n,,1,0),
      M, IF(ISNUMBER(matrix), matrix, 0),
    MMULT(M,k)))

No prizes for brevity for me.  I am still waiting for the functionality to be enabled on my 365 licence. 

The other strategy I had wondered about was to use INDEX to select a specific row and then call the function recursively.  Once all the rows are visited, a blank result column is created and, following each return, the already calculated sum is inserted into place within the column.

One day, I will be able to try out these ideas! 

[#18]

@Twifoo 

Glad these old posts inspired you to post a challenge such as this which has generated many interesting replies!

@keenadvice 

Yes these exercises are mostly academic but can be fun. If you want to pursue these techniques further I'd suggest looking at Jupyter Notebooks (Python or R) which now provide a great way to do interactive analysis on arrays and are increasingly used in the financial modelling community

@Peter Bartholomew 

horses for courses. For generic functions Ithat have been well tested performance is a consideration so I'd avoid LET in this instance.  Best wishes for 2021!!

[#19]

@lori_m 

Agreed. 

Happy New Year 2021 to you and others here.

There is plenty of room for improvement over 2020 for most!

[#20]

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? 

[#21]

@Twifoo 

All quiet on the fun challenge-quest I notice: no doubt all of us are too busy wrestling with real-life spreadsheets!

For the record, this is what I came up with for #9 and #10.  No curly brackets (I am sure you could get a bit shorter using those as previous).

All a good excercise in practising, seeing what you can do with dynamic arrays, helping keep us all match-fit for our day jobs I am sure!!

Thanks @twifoo!

#9 = list of top performing branches, 172 characters

=LET(
Profits,
MMULT(
TRANSPOSE(
(Branches=SEQUENCE(1,9))*
SWITCH(Types,"cost of sales",-1,"operating expenses",-1,1)),
Amounts),
IF(SEQUENCE(9)<=5,
SORT(Profits,1,-1),""))

Result:

28200

20500

9700

7900

3600

#10 = as above but adding the branch number, 260 characters

=LET(
Profits,
MMULT(
TRANSPOSE(
(Branches=SEQUENCE(1,9))*
SWITCH(Types,"cost of sales",-1,"operating expenses",-1,1)),
Amounts),
Ranking,
SORT(Profits,1,-1),
IF(SEQUENCE(9)<=5,CHOOSE(SEQUENCE(1,2),
"Branch "&XLOOKUP(Ranking,Profits,SEQUENCE(9)),
Ranking),""))

Result:

Branch 4   28200

Branch 6   20500

Branch 1   9700

Branch 7   7900

Branch 5   3600