Forum Discussion
Formula Challenge: Create a guitar fretboard diagram
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))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).
- interesting. I bet that time difference is related to using MATCH vs XMATCH and maybe CHOOSEROWS vs INDEX