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]

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

[#3]

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.

[#4]

@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))

[#5]

I prefer to work with A to G as a constant because I know the #s and b (flat) notes add their respective characters to most (but not all) letters. Plus, I'd rather use REDUCE than type up a long constant!

I had considered not providing the strings (E-A-D-G-B-E) for this challenged but figured someone would create a constant anyways.

For what it's worth, the calc timings are about the same. Mine is .00086 seconds vs yours at .00091 (5 timings were averaged).

[#6]

interesting. I bet that time difference is related to using MATCH vs XMATCH and maybe CHOOSEROWS vs INDEX

[#7]

Swapping MATCH for XMATCH reduces the timing to 0.000834.

A solution using SCAN instead of MAKEARRAY would probably decrease that time even more.  

[#8]

@Patrick2788 

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!

[#9]

@PeterBartholomew1 

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.

[#10]

@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)

[#11]

@mtarler 

Matt, I had thought of using text strings rather than arrays and had gone the array route, but I must admit the text can be simpler.  What I have now tried, based upon your solution, is to use fixed length rather than comma separated sub-strings (I used 'underscore' in place of a 'space' simply to make the character visible).  

The text string that represents the tuning of a single guitar string is given by the Lambda function

Tuneλ(s)
= IF(s <> "", 
     LEFT(TEXTAFTER(Scale, s & "_"), 24), 
     REPT(" ", 24)
  )

Note: the Lambda call and its parameters are inserted by the AFE

Whereas the Lambda helper functions are all crippled to prevent nested arrays in the interest of maintaining compatibility with some mythical past, the MID function suffers from no such inhibition, and will use lifting to generate arrays of arrays, no questions asked.

As part of my drive to turn every formula to a Lambda function, I also used

SplitByFretλ(StringTuning)
= MID(StringTuning, SEQUENCE(1,12,1,2), 2)

"// giving the worksheet formula //"

= SplitByFretλ( MAP(Strings, Tuneλ) )

[#12]

The average calc time was .00081 for this version.

Re: the previous version
I did some testing with MATCH/XMATCH with the lookup vector being about 95,000 unique elements. At first, I did a simple match of one number that was in the first position in the array - no difference in calc times. Next, I picked the last element and again no real difference in calc times. Finally, I pulled 100 items from the lookup array to be matched and spilled XMATCH/MATCH and again no real difference in calc times.

[#13]

@Patrick2788OK let's see if we can trim some time by removing LAMBDA:

using textjoin/textsplit:

=TEXTSPLIT(
      TEXTJOIN(";",FALSE,
         IF(Strings<>"",
             MID(Scale,SEARCH(Strings&",",Scale)+LEN(Strings&","),28),
             REPT(",",11))),
      ",",";")

going 'old school' except SEQUENCE:

=TRIM(IF(Strings<>"",
                  MID(
                     SUBSTITUTE(MID(Scale,SEARCH(Strings&",",Scale)+LEN(Strings&","),28),",",REPT(" ",28)),
                     SEQUENCE(,12,,28),28),
                  MID("",SEQUENCE(,12),1)))

[#14]

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.

@PeterBartholomew1

[#15]

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)

[#16]

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

[#17]

which one I had 2 in that file (& post) or did they each perform the same?

[#18]

That was for the top one. The bottom solution clocked in at .00083 (Average of 5 timings).

[#19]

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.

@Patrick2788

[#20]

yes I too have had some dreadful HTML error issues. I wonder what changed.