Formula Challenge: Create a guitar fretboard diagram

[Patrick2788]

This exercise has helped in my ongoing studies of Excel and learning acoustic guitar.

The challenge is to produce the standard tuning (E-A-D-G-B-E) diagram:

[This diagram lists 'natural' notes (Simply the letter) and 'sharp' notes (letter w/#) - ('Flat' notes not included in this diagram).]

You are only provided the strings - EADGBE (The first column from the diagram).

I will step through my solution.  You can skip to the attachment if you want to dig in and not be influenced!

Named item 'Strings'

=Guitar!$C$3:$C$13

'Standard' - named after the tuning type.  I went with an array constant for the letters because I think it reads better than using CHAR-SEQUENCE and it's still pithy.  If the letter is B or E (letters w/o sharp notes), do nothing, otherwise keep the letter and string together a sharp note with it.  TEXTSPLIT to split the scalar into a vector.  HSTACK then stacks the vector twice which is key because the notes are pulled from left-to-right in the next formula.

=LET(
    notes, REDUCE(
        "",
        {"A", "B", "C", "D", "E", "F", "G"},
        LAMBDA(a, v, IF(OR(v = "B", v = "E"), a & "," & v, a & "," & v & "," & v & "#"))
    ),
    split, TEXTSPLIT(notes, ",", , 1),
    HSTACK(split, split)
)

Lambda 'Fret'.  I opted for MAKEARRAY because I view this exercise as an 'unpacking' and it allows me an easy way to generate the blank rows.  'Tuning' is the lone parameter because I may update this to accommodate several more tuning diagrams.  The various diagrams I've studied have different patterns so it would require an investment of time.

=LAMBDA(tuning,MAKEARRAY(
    11,
    12,
    LAMBDA(r, c, IF(ISODD(r), INDEX(Tuning, XMATCH(CHOOSEROWS(Strings, r), Tuning) + c), ""))
))

I'm interested in different approaches to this task. All are welcome!

[#2]

@mtarler

I will try to post a code snippet.

Another place where arguments are read from left to right is Curried functions

= LAMBDA(x,LAMBDA(y,LAMBDA(z, (x+y)/z)))(3)(5)(2) 

gives 
= (3+5)/2 = 4 

or, with naming, 
CurriedLambda 
= LAMBDA(x,LAMBDA(y,LAMBDA(z, (x+y)/z))) 

= CurriedLambda(3)(5)(2) also gives 4 

The final parameter can also be provided by a helper function

= MAP({2;3;4}, CurriedLambda(3)(5)) 

gives 
= {4.00; 2.67; 2.00} 

[#3]

Seems to work. I don't know what was happening before.

[#4]

@Patrick2788 OK I have 2 more variations:

=IFERROR(INDEX(strSeq,,SEQUENCE(,12)+XMATCH(Strings,strSeq)),"")

=IF(Strings<>"",INDEX(strSeq,,SEQUENCE(,12)+XMATCH(Strings,strSeq)),"")

I'm betting on the 1st ...

[#5]

Timings: .00087 and .00084

I re-ran the calc timings removing the IFERROR and IF, respectively and the timings were about the same.

[#6]

@Patrick2788 

@Peter Bartholomew 

@mtarler 

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)

[#7]

@Patrick2788 

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.

[#8]

@Patrick2788 

This is the other approach I mentioned in passing.  It uses your basic function to calculate each row of four faces individually before collecting them together using a further REDUCE/VSTACK.

[#9]

@Peter Bartholomew 

This solution (first solution) reads very well. The consistency in the parameter names really helps me follow the parameter pass between functions.  I played with changing the numbers in the ArrConst and sure enough the result still produces perfect blocks of numbers no matter the order.

I see the alternative solution has some similarities to the first one but differs in delivering the solution in 'Rubik'. I prefer the first solution because I think it reads a bit better and its version of 'Rubik' is very clean.  I'm still trying to grasp the full potential of REDUCE and going beyond the limited notion I had of the function that can 'merely return a scalar'.  REDUCE seems to be a cut above the other Lambda helper functions.  Perhaps it wouldn't be as useful if the array of arrays limitation was lifted but it's very powerful.

[#10]

@Patrick2788 

I would consider the failure of SCAN, BYROW, BYCOL to generate nested or stacked arrays to simply be a gross failure in the original specification.  It should have been obvious that the majority of applications of such functions requires arrays of arrays.  The exceptions are when one is calculating (i) a single value such as a grand total, (ii) a 1D row or column vector, (iii) a homogeneous 2D matrix, typically using array operations such as MMULT.

REDUCE only aims at producing a terminal value so it is capable of processing a 2D array, column by column.  The use of HSTACK, is an effective workaround to make REDUCE return the result that SCAN should have generated by default.

What it does not achieve, (see attached) is to provide support for the case that one uses array methods to simplify calculation, not add complexity!

[#11]

@Peter Bartholomew

It's interesting how REDUCE provides a workaround but SCAN cannot handle nested arrays. I view SCAN and REDUCE as 'sister' functions in some regards because in many cases I can "check my work" with REDUCE by swapping it out for SCAN.
I do like using REDUCE to spill when it's not intended to do so. It's a nice workaround but it should be easier, I agree with that.  The other workaround being text manipulation which feels like cheating with how often I've resorted to using it.  It's not quite using helper columns in legacy Excel but it's getting there!

For me, BYROW/BYCOL are used sparingly.  When I go through progressions in my head of which functions can be used to achieve a task, those two are near the bottom of Lambda helper functions.  Although, I do like using BYROW with FILTER to simplify multiple column criteria.