Using Excel Formulas to Convert a PianoRoll Spreadsheet to StaffKlavar1

In previous entries I described techniques for creating simple alternative-notation scores using just word processing and spreadsheet software and a home-made font. I used the spreadsheet only to create and format a table.  I did not use formulas or calculations, but the process required a lot of manual editing.  In this entry, I discuss using spreadsheet IF-formulas to eliminate a lot of the manual editing required for the method described earlier. I believe these techniques may be useful for musicians or teachers who want to experiment with alternative notations but do not have the resources to acquire or produce dedicated software.  Furthermore, although I am using specific variations of “PianoRoll” and “Klavar” notations in these examples, the techniques are not limited to these particular notations, but  could be applied to various Alternative Notation proposals.  I am hopeful that facilitating the creation of computer-editable scores in various notations will contribute to the eventual development and dissemination of notations that make playing and even composing music more widely accessible, especially to beginners of all ages. Recall from my previous music blog entries that PianoRoll and Klavar are alternative music notations that use a “staff” in which every black and white key note has it’s own line or space, unlike traditional notation in which the black key notes share positions with white key notes.  There is a line for each black key note and spaces for the white-key notes.   To simplify creating and printing staff notations with office software, I run the  “staff lines” vertically down the page, with the pitches distributed across, so the staff appears rotated 90 degrees from the conventional staff orientation.  (Klavar itself is typically played with the staff vertical, but if you would rather read your music from a horizontal staff, just rotate the page after printing.)   An example of PianoRoll is given in Figure 1, left side, below; Klavar is shown in Figure 6.  If you are not familiar with the details of the “PianoRoll” or “Klavar” notations, please read my earlier blog entries first, which include more complete examples. My version of Klavar uses a custom font, StaffKlavar1, which I developed with a free on-line system FontStruct (  (You can download the font from that site, and also make modifications or create your own fonts.)  The StaffKlavar1 font is designed to use two characters per chromatic scale position, one character for a line symbol and the comma character for a half-space. There are several versions of the “line” character:  black and gray, whole (for unoccupied lines) and partial (for lines with notes on them), even a “white” line used between two half-space characters to make a 3/2 space for BC and EF.   For note symbol characters I use digits representing finger numbers: 12345 for the right hand and 67890 for the left.  A note symbol replaces three staff characters: one of the line symbols and the two surrounding commas that represent the half-spaces. To convert a PianoRoll spreadsheet to StaffKlavar1 format, you must edit the PianoRoll sheet (which contains only note symbols, no line or space symbols) to add the staff line characters,  then remove separator commas around the notes, then change the font.  To facilitate removing the extra commas, I start by surrounding the notehead symbols with an otherwise unused character, the semicolon ( ; ). The following figure illustrates the desired result: The original PianoRoll is on the left; the same score with staff-line characters and semicolons added is on the right.  The numbers in the first row of the PianoRoll section are MIDI note numbers for reference; these will not be included in the StaffKlavar version.)
Figure 1: Example of PianoRoll to StaffKlavar1 Conversion: Left side is PianoRoll; Right side includes added staff characters. Columns for notes 37..53 and 60..71 were hidden to improve legibility, but follow the same pattern.
The preceding steps are described in detail in my previous blog entries. Now adding the staff-line characters and semicolons can be a tedious operation, as it requires modifying every cell in the PianoRoll spreadsheet. Furthermore, adding staff-line characters destroys the transposability of the PianoRoll score.  As long as the score is in PianoRoll format, it is key-invariant; that is, you can transpose the music to a different key simply by copying the same pattern of note symbols to another set of columns, as long as you maintain the relative spacing. With Excel, you can copy just these value patterns with the “Copy” and “Save Special  >  Values” operation. This is especially useful for transposing exercises into other keys and for copying chord note and finger-patterns to other chords. But once the staff-line characters have been added, the key-invariance is lost; changing keys requires moving the note numbers relative to the staff-characters.  This cannot be done with a simple “Save Values” operation, because the note numbers and the staff characters are both “values”. So I wanted a way to merge a PianoRoll spreadsheet containing just the note symbols with another spreadsheet formatted as “blank staff paper.” I had originally considered writing a computer program to convert do this, but it turns out that the “merger” can be done within the spreadsheet itself, using the IF function. This is the process: 1. Begin by creating your PianoRoll score in a spreadsheet, leaving row 1 without any notes (in my example, I filled row 1 with MIDI note numbers, 60=middle C). Let’s say the score is in columns A:AZ (use more columns if your piece has a range over 4 octaves). 2. Build the “blank staff paper” alongside it, say columns BA-CZ, as follows: 3. In the first row of blank cells BA1:CZ1 at the top of the “staff paper”, enter the pattern of staff line characters, based on the following table  (the “zero” row represents midi note numbers, which are for reference only, do not enter them into the staff paper itself):
Figure 2: Klavar Staff characters for each note of the chromatic scale.
Notice that staff line characters appear twice for each black note. The brackets [ and ] represent an empty solid line segment, with no note; the braces { and }  represent just the portion of the line extending above and below the note, and actually print in the position to the right of where they are entered. (I do not run lines through the notes to avoid obscuring the finger numbers.)  A note symbol appearing in a “black” column will replace the solid line, so only the outside segments will appear in the score; if both symbols occur, they merge into one.    The vertical bar | represents a “white” line between two half-spaces in the three half-space BC and EF intervals. Now we want to fill the “score” with either the staff line symbol, if there is no note at that position , or notehead symbol surrounded by semicolons, when there is a note.  This is easily accomplished with a spreadsheet “IF” formula. For an example, consider the following notes from the PianoRoll score:
Figure 3: Example line in PianoRoll notation; numbers in cells represent finger numbers
This represents three notes played together: a G and Bb in the bass played by the left hand pinky and middle finger, with an Eb in the Treble played by the right hand middle finger. For those not familiar with the more advanced features of Excel formulas: an IF function in a cell chooses one of two different formulas for the cell value, depending on the value of a third formula.  The IF function takes the form:
=IF( logical-test, value-if-true, value-if-false) .
The first expression in parentheses, logical-test, will typically be a comparison and evaluates to TRUE or FALSE. If the first expression is TRUE, the second expression value-if-true becomes the value of the function, or, if the first expression is FALSE, the third expression value-if-false becomes the value of the function.
Figure 4: Example of formula conversion of Bb (note #58).
In our case, we want the IF to choose between the note from the PianoRoll or the staff symbol from the first line of the Klavar score.  To see how this works, let’s look at the Bb in the example.  The original PianoRoll note symbol is in cell Y26, and the corresponding StaffKlavar symbols will be in cell BY26.  The IF formula is entered in the cell BY26 in the StaffKlavar part of the sheet.  The actual formula entered appears in the cell contents display at the top of the spreadsheet, and the formula’s value displays in the cell itself. The complete formula entered in cell BY26 is =IF(Y26=””,BY$1,”;”&Y26&”;”) Here, the logical-test (first) expression is:  Y26 = “” where the two quotes with nothing between represent the NULL value, signifying an empty cell. The equal sign = indicates that the value in Y26 is compare to “empty”; if Y26 is empty (no note) the value is TRUE; otherwise (note present), the value is FALSE. The value-if-true (second) expression is BY$1 which represents the value in cell column BY row 1.  Recall that we previously filled row 1 with the staff characters for corresponding note positions. The $ before the 1 indicates that formula will always use row 1 no matter which row the formula is copied to. The value-if-false (third) expression is “;” & Y26 & “;”; the & symbols are operators that create a string composed from the three parts: the semicolon (contained inside the first set of quotes),  the value in cell Y26, and another semicolon. The cell Y26 contains the note character (if any) in the PianoRoll sheet. The full formula says: if cell Y26 is empty, copy the character from column BY, row 1 (this is the staff line character; in this case: ] ) into cell BY26; otherwise, put in a semicolon, followed by the character in Y26, (the note character; in this case: 8), followed by another semicolon. In this case, since Y26 is not blank, the formula chooses the note character, evaluating to: ;8; Composing and entering this formula is tedious enough, but fortunately we only need to do it once! We can use the Excel copy function to copy the formula from BY26 to all the other cells in the StaffKlavar side of the spreadsheet at once! (In practice we would have started with cell BA2, but it doesn’t really matter.)  Excel will automatically adjust the row and column numbers to correspond to the actual location of the formula.  Except, of course, the $1 does not change but always refers to row 1, which contains the staff line character for the column. The following figure illustrates how the formula adjusts to fill all the cells.  Notice the form of the formula is the same, but the row and column numbers adjust to copy all the cells of the PianoRoll sheet.
Figure 5: Example of PianoRoll to StaffKlavar conversion with formulas expanded.
For example, cell BW28 will contain the formula : =IF(W28=””,BW$1,”;”&W28&”;”), which is essentially the same as the previous formula, except using row 28 instead of row 26, and columns W and BW instead of Y and BY. In this case, since W28 is empty, the staff line character ] from BW1 will be copied to BW28. Similarly, cell AP26 will contain the formula:  =IF(AP26=””,CP$1,”;”&AP26&”;”), which is a similar formula operating on columns AP and CP. Since AP26 contains the note-digit 3, this will be inserted, between semicolons, in cell CP26. (see Figure 4.) Up to now, the editing to convert the PianoRoll has consisted of: * adding one row of staff-line characters * entering a single formula in a single cell * doing a global copy of that formula to the rest of the PianoRoll copy. In fact, all of these operations could have been done a priori and saved in a template, which could be copied in in one step. At this point, the Klavar version of the sheet must be isolated and saved as a .csv prior to further editing with in a Word Processor:
  1. Copy the edited StaffKlavar version of the score from columns BA:CZ
  2. then Paste Special > Values to a new spreadsheet columns A:AZ,
  3. Save the new StaffKlavar-only sheet as a comma-separated-values format file (.csv).
  4. Open this file as a document file in a word processor, and further edit it as follows:
  5. Covert comma-semicolon and semicolon-comma pairs to null strings, to remove the excess commas (representing half-spaces) surrounding the note characters.  (This “space” will be occupied by the notehead symbol, whose width equals that of one line and two half-spaces.
  6. Change the font for the staff columns of the file to StaffKlavar1 font.
  7. The document will now look somewhat like a musical score, but with the staff oriented vertically instead of horizontally, as illustrated in the following figure:
Figure 6: Example of StaffKlavar file after word-processor editing is completed and the font is changed.
You can adjust the font size to fill the page, convert to multi-column format if you wish, and add bar lines using the underscore feature of your Word Processor. If you wish to add additional non-note information to the score, such as measure numbers or chord names, it is most convenient to convert the score in the Word Processor to a table format.  Adding separator columns of special characters to the StaffKlavar spreadsheet before saving the .csv facilitates the “text to table” conversion.  The table format simplifies converting just the notes portion to the new font.  After changing the font, you can convert the table back to text. Although my example does not illustrate this, one could add additional IF functions, or the more general VLOOKUP, to change the notehead shape or color depending on the staff position.  For example, “official” Klavar notation has noteheads of two different colors: black notes on lines and white notes on spaces.  In the StaffKlavar1 font, to get “white” notes, use the capital letters YUIOP QWERT in place of the “finger numbers” 67890 12345 that give “black” notes. (Note: official Klavar also places white and black notes on opposite sides of the stem. The StaffKlavar1 font does not support this, but in principle a font could be created to support this feature. I have not made this a priority because, for my personal uses, I do not want to consume double the “real estate” on the page by using two rows per note.) Conclusion:  This process demonstrates that a simple Alternative-Notation score can be produced from a PianoRoll spreadsheet using just global editing operations within spreadsheet and word processing software. For now, this technique is limited to simple scores.   And surely specialized software could produce the score more efficiently. But if one is exploring variations of notations, it may not be worth the effort to create or customize specialized software for each experiment.   Thus techniques such as these may be useful to investigators attempting to evaluate a range of notations or tweak an individual proposal.  They may also be useful for composers, performers, teachers or students who want to experiment with alternative notations but do not have the resources to acquire or produce specialized software. If you have suggestions, questions or would like additional details, let me know.

About DrTechDaddy

"Dr. Tech Daddy" is Joe Austin, a retired computer science professor. My interests include: software development (web and mobile), STEM and music education; learning to play the piano; mathematical foundations of computing; popular theoretical physics; model railroading (G, N, Thomas); Lego robots; history and theology of the Judaeo-Christian Church.
This entry was posted in Music, Non-Traditional Notation Editor. Bookmark the permalink.

Leave a Reply