Forum Discussion
Formula Challenge: Create a guitar fretboard diagram
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!
Patrick2788 maybe I don't understand but I took a different approach and included the # notes. First I defined the string sequence (going down the frets) and doubled the length to avoid dealing with wrap around:
={"E","F","F#","G","G#","A","A#","B","C","C#","D","D#","E","F","F#","G","G#","A","A#","B","C","C#","D","D#"}then I did a lookup + offset:
=MAKEARRAY(11,12,LAMBDA(r,c, IF(ISODD(r), INDEX(strSeq,MATCH(INDEX(Strings,r),strSeq,0)+c), "")))NOTE: if you want to specifically want to exclude the # notes then wrap a Let around it and add a statement like IF(len(pp)>1,"",pp) to blank out all the # notes.
EDIT: OK so I just figured out what you are doing. you planned on using =FRET(STANDARD) as the formula to generate the grid and your FRET() function is basically identical to what I came up with. So the only difference is that I created strSeq constant for STANDARD. Literally I used =FRET(strSeq) and got the same result
馃檪
EDIT2: OK so I'm guessing you're going to call foul on me because you say you only know "EBGDAE" and I created that constant with the # in it. But I claim you can't possible create that STANDARD function with out knowing that sequence.
I created the sharp notes because I felt like creating a constant for all notes would be a bit much (and potentially not sustainable if I add more diagrams that include 'flat' notes). Originally, I had created sharp notes for letters A to G and then filtered out the non-existent # notes. It comes down to preference with this exercise. I would rather produce what I need than produce more than I need and remove the non-essential.
Patrick2788 hmmm, would you prefer something like:
Standard ={"E","F","F#","G","G#","A","A#","B","C","C#","D","D#"} Flist = LAMBDA(tune, HSTACK(tune,tune))
Hi Patrick
I have played with the challenge and achieved a different solution by with no claim of having made it better!
The idea of creating a doubled-up scale appeared to be a good idea, so I created
altStandard = LET( scale, {"A","B","C","D","E","F","G"}&{"";"#"}, deselect, IF((scale<>"E#")*(scale<>"B#"),scale,NA()), notes, TOROW(deselect,2,TRUE), HSTACK(notes,notes) )The formula replaces B# and E# by errors and then uses TOROW to reorder and filter out the errors. Then I wrote a Lambda function to select values by fret for a single string choice
Fret位 = TAKE(DROP(altStandard, , XMATCH(string,altStandard)),,12)I used the REDUCE/VSTACK combo to generate the full output array
WorksheetFormula = REDUCE(fretLabels, Strings, Build位) Build位 = LAMBDA(tuning,string, VSTACK(tuning, IF(string <> "", Fret位(string), EXPAND("", 1, 12, "")) ) )For me, it provided an excuse to use some of the new array shaping formulas as an integral part of the calculation. That is not yet part of a process that comes naturally!
I do like the use of TOCOL/TOROW to 'filter' data without using FILTER. Helpful to remove 0s and errors and probably quicker than FILTER.
You seem to be getting really good at creating a function and then calling the function within another LAMBDA as is the case with Build位 inside of REDUCE. I've gotten REDUCE to spill here and there by accident, but this is a method that I'm going to study.
TAKE-DROP is a clever swap for INDEX and probably faster, too. It's interesting that TAKE/DROP are accepted within legacy menus like Data Validation and are a better option than OFFSET/COUNT(A) for dynamic ranges.
Patrick2788 as long as you are doing speed tests I'm curious how a string based solution fares:
Std = "E,F,F#,G,G#,A,A#,B,C,C#,D,D#," Scale = Std & Std =DROP( TEXTSPLIT( REDUCE( "", Strings, LAMBDA( p, s, p&IF(s<>"",LEFT(TEXTAFTER(Scale,s&","),28),REPT(",",11))&";") ),",",";"),-1)
I tried to answer but I couldn't get past the dreaded HTML error!
Basically, you are correct in that parameters are passed left to right.
Starting out with anonymous Lambda functions may help with the syntax, and then the code, starting with LAMBDA and ending with the closing parenthesis, are replaced by the Lambda name.
- yes I too have had some dreadful HTML error issues. I wonder what changed.
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 4The final parameter can also be provided by a helper function
= MAP({2;3;4}, CurriedLambda(3)(5)) gives = {4.00; 2.67; 2.00}
I'm trying to fully understand passing multiple parameters. I've created a sample Lambda (its name is the name of a measurement system but for some reason cannot be posted here?):
I then call this function within MAP.
This is the part that was a bit foreign to me. I'm not providing the function with its parameters within parenthesis but rather, it seems to be inferred the function is pulling them left-to-right from the 3 arrays I've given MAP. I hope I'm understanding this clearly.
Edit: the formula snippets cannot be posted because of the function name it seems. Please see attached workbook.
- it appears you reversed your imp and met in the call in the cell:
=MAP(E2#,imp,met,Imperial)
should be
=MAP(E2#,met,imp,Imperial)- Good catch. I was testing the order of the arrays and forgot to switch it back.
Your latest version w/o Lambda: .00088 average calc time
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.
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.
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.
