Forum Discussion
Formula Challenge: Create a guitar fretboard diagram
For some more practice with the method Peter has outlined in this discussion, I've attempted to apply this approach to a Rubik's cube exercise I've been playing with every now and then.
The goal is simply to create this array:
My previous solution involved creating the middle 1-4 blocks and then stacking and expanding the rest. Not exactly elegant but it got the job done.
With the new approach, I started with an array constant:
={5,"","",1,2,3,4,"",6,"",""}Created a 3x3 matrix of blanks
=EXPAND("",3,3,"")Created the Lambda called 'MStack', intended to be used in REDUCE
=LAMBDA(matrix,v,HSTACK(matrix, EXPAND(v, 3, 3, v)))Next, comes a named item called 'Matrix'. My intent was to deliver the solution at this step, but it needed 1 more step.
=REDUCE(Blanks,ArrConst,MStack)A Lambda - 'Rubik'
=LAMBDA(Stack,VSTACK(TAKE(Stack, , 12), TAKE(DROP(Stack, , 12), , 12), TAKE(Stack, , -12)))Finally, at the sheet level:
=Rubik(Matrix)
I did come up with an alternative formula closer to your original, but that is not the purpose of this discussion.
I was happy with the individual formulae but less comfortable with the way they are strung together. As I see it, the choices are
1. Nesting the formula (traditional but pretty much unreadable)
2. Using defined names to hold the inner formula (hides their content and it is difficult to keep track of multiple drill-down steps)
3. Using Lambda functions to perform inner calculation (also hides content but a well-chosen name and a list of arguments may mean that drill down is not required)
4. Using LET to present the calculation in steps with intermediate results held in local variables.
I aim to balance the final two, treating LET as part of an outer Lambda and using it as if it were a program main routine. If the calculation steps get too long or it contains well-defined subtasks, then I resort to further enclosed Lambda functions, here used as modules to shorten the main 'routine'.
In the present case, the architecture I finished with is an outer function calling two sub-functions.