Building a PBC in Google Sheet
This article is part of the series Building a Process Behavior Chart from data collected via Apple Shortcut. The full list of articles is:
In the previous article, I shared what Process Behavior Charts (PBC) are and promised to explain how to build one in Google Sheets, using the data collected from an Apple Shortcut. This article will cover that. Let’s get started.
The article on collecting the data left us with a Google Sheet containing one sheet of data with all the measurements collected from the shortcut. You can access that spreadsheet from Google Forms by clicking on the "Responses" tab.
You can rename the first sheet, containing the measurements, to something that suits you better than "Responses." I decided to rename it "Measurements."
You won’t need to touch that sheet anymore; we will work on another sheet for the rest of this article.
Do not work on the sheet made for collecting data
When working with spreadsheets, having a sheet that contains raw data helps with maintainability since you won’t be tempted to move columns around and break formulas elsewhere. If you’re into Software Architecture, you can view that as your write side in the CQRS pattern, and we will use other sheets for the read sides.
Fetch data from the form submissions sheet to build the PBC
Create another sheet where we will compute the PBC data for the measurements. I named mine "PBC." You can’t make it more straightforward than that.
In that sheet, get the data from the other sheet with the ={Measurements!A2:B} formula in the A2 cell.
In the first row, in cells A1 and B1, add titles for the two columns. I went with "Date" and "Measurements."
Why aren’t we getting all the data from the reference data sheet ?
Why not get the value directly from the A1 cell to get the column’s name from the reference data sheet?
In the A1 cell, we could use ={Measurements!A:B} to get all the data directly from the Measurements sheet. That seems like a good idea, but it actually isn’t.
Google Forms keeps track of how many submissions were received and adds new submissions to the current count + 1 row. This is probably to avoid overwriting past values.
During the setup process of the Apple Shortcut, you will be tempted to test it and see if everything works as expected. Unfortunately, due to Google Forms' ways of working, you won’t be able to insert real, non-test data starting from the first row.
Using the trick above, you can select the row at which your PBC starts reading real values.
If the real values submitted from the form are inserted in row 17, you can edit the formula in the A2 cell to reflect that, using ={Measurements!A17:B} to get only the data you really care about in that sheet.
You should now see values from the form in that spreadsheet. Each new value submitted via the form will end up in that sheet.
Compute the moving range
In the first row of the C column, write "Moving Range."
The moving range is the absolute difference between one measurement and the one before. We can’t have a moving range value for the first measurement, as no previous measurement was made. In the C3 cell, add this formula: =IF(ISBLANK(B3), "", ABS(B3-B2)).
ABS(B3-B2) computes the difference between the second value and the first one and makes it a positive number.
The other part of the formula prevents incorrect values from being displayed in rows without any measurement yet by leaving them blank. We will use this trick for all the other columns.
Use the blue dot and drag down to set the formula for the following rows[1]. I went down to row 100.
Compute the average
In the first row of the D column, write "Average."
I decided to go with 20 points for my baseline. To do the same, add =IF(ISBLANK(B2), "", AVERAGE(B$2:B$22)) to the D2 cell.
Again, use the blue dot and drag down to set the formula for the following rows.
Compute the average moving range
In the first row of the E column, write "Average moving range."
For a baseline of 20 points, we need to compute the average of the first 20 moving ranges.
To do this, add =IF(ISBLANK(B2), "", AVERAGE(C$2:C$22)) in the E2 cell and use the blue dot once more to apply the formula for more rows.
Compute the Lower Limit
In F1, write "Lower limit", add the =IF(ISBLANK(B2), "", D2 - (3 * E2 / 1.128)) in F2, and use the blue dot.
Compute the Upper Limit
In G1, write "Upper limit", add the =IF(ISBLANK(B2), "", D2 + (3 * E2 / 1.128)) in G2, and use the blue dot one final time.
Now, you have all the data you need to draw your XChart, the first of the two types of charts usually included in PBCs, and the only one we will build together.
The other chart, the mRChart, helps detect signals of unusual variation between two consecutive measurements.
You’ll often see the pair of the two charts referred to as XmR Charts.
Draw the graph
Select columns A (Date), B (Measurement), D (Average), F (Lower Limit), and G (Upper Limit), and insert a new graph.
You’ve drawn your first XChart with Google Sheets. Congrats!
It’s probably unreadable, though. Please take a few minutes to adjust the settings and improve it.
First, zoom in to view your data. In the vertical axis settings, add a minimum value.
Also, change the graph title to give it a better name.
Now that we see more than just a group of compacted lines, we can customize the series.
For the Measurement series:
- Make it black
- Add the data labels
For the Average series:
- Make it blue
For both the Upper and Lower Limits series:
- Make them red
And now you have a nicely displayed PBC, or more precisely, the XChart part of it.
If you’re interested in topics like this and are looking for ways to improve your organization, let’s have a chat! I help software companies deliver better products faster by working at the crossroads of technical mastery, product thinking, and organizational processes.
If someone knows a better way to set a formula for all following cells in a column, I’d be more than happy to hear it. ↩︎
