# Using Excel Formulas to Convert a PianoRoll Spreadsheet to StaffKlavar1

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