Google Sheets - Making a chart where certain series do not have data for all X-axis values

Question

I am making a chart for research in musicology which requires me to graph the duration and tempo of notes based on the score, i.e. musical values like a quarter note.

Using a recording of a string trio I have extracted note onset (beginning of the note) values in milliseconds for each instrument which I converted to bpm. I want to graph said values against the X-axis (beat or quarter note grid) to examine how they stretch or compress the tempo of a given excerpt and how together the ensemble is based on how close "simultaneous" attacks are to each other.

The issue is that not all instruments play notes on the same beats. For example, instrument A and B will play a note on the 3rd beat while instrument C plays on the 2nd beat and holds said note through the third beat. Whenever I try to make a chart for this google sheets or excel will assume that the X-axis is common for all series so it will take the first bpm value of instruments A and B and place it on the 2nd beat since the series for instrument C has a value there.

Is there a way to make a "universal" X-axis, where I can put for example all quarter notes but then graph the tempo of each series ONLY on the beats in which each instrument actually plays?

To use the example above. I want to make a chart where even though the grid shows beat 1, the series for instrument C ignores it and puts the first data point on beat 2. And for instruments A and B, it ignores beats 1 and 2 and puts the first data point on beat 3. See image below.

As with any musical work sometimes instruments will play the same beats, sometimes they will not. I just need to graph the evolution of the bpm through time of all instruments on one chart with the subdivisions of the score in the X-axis.

Answer 1

I think the biggest issue is the way you structured your data because Excel does not recognize then that the beat should be your x-axis. Thus, I would suggest slightly changing the order of your data starting with the beat. Given that you said "C plays on the 2nd beat and holds said note through the third beat", I am not quiet sure whether you also want to repeat the values, thus, I show both situations (Option 1 left and Option 2 right, respectively):

Beat	A	B	C		Beat	A	B	C
1					1
2			70		2			70
3	72	71			3	72	71	70
4			72		4	72	71	72
5	70	69	70		5	70	69	70
6					6	70	69	70
7		72			7	70	72	70
8	72	74	75		8	72	74	75

Based on this restructured data you can insert the graphs.

Option 1: Select the corresponding data -> go to Insert -> Recommended Charts -> All Charts -> Line -> Line with Markers -> Option 4 in the bottom right corner.

Option 2: Select the corresponding data -> go to Insert -> Recommended Charts -> All Charts -> Line -> Line with Markers -> Option 2 in the top right corner.

The resulting graphs look as follows: