[Update 20 Nov 2018: The very latest version of the Google Sheet for creating burn-up charts is available at Github. It also has detailed instructions. Please go there instead. Otherwise there is a history of articles which are out of date: from 2011, 2014, 2015, 2016 and 2018.]
A few months ago I published an easy way of making burn-up charts with Google Sheets. It’s less sophisticated than any paid-for product, but it combines the flexibility and familiarity of a spreadsheet with traceability (which I find is essential if you need to check back on things).
Since that time I and others have put it to use, and now I’ve added two new functions. Both these functions are intended to help you get a better view of the work in progress at any moment in time. By all means go and copy the updated spreadsheet (File > Make a copy…). Brief instructions are included.
If you want to know details, and if you’ve familiarised yourself with the basics, then read on…
New function: Get all values for a given date
This is useful if we want to get an easy view of our work as it was on any particular date. This can be a bit tricky normally, because the raw data lists every version of every user story, and different stories will change on different dates.
So I’ve added a function called getValid. This effectively says “For a given date get all the values of a given field”. The parameters are:
- The date in question;
- Three parameters specifying the raw data: the range of the data (including the header row), the column name of the story ID, the column name of the date from which this row’s data is valid;
- The column name of the field we want to show.
That’s a bit abstract, so here’s an example from the demo spreadsheet. You can find it in the tab named Listing example.
Our raw data looks like this:
To just get a burn-up chart we’d use the sumValid function described in the earlier article. But to get all values that were valid on a given date we use getValid. Let’s suppose we want to see the stories that were known on 29 June 2016. In particular we decide we want to list three fields: the story id, its name, and its estimate. That means we want to use a getValid forumula three times: once for the id, once for the name, and once for the estimate. Here’s what the first formula looks like:
The formula itself uses the parameters we gave above: the date in question, the details of the raw data (which happen to be in a named range: StoryDim), and the name of the column we’re interested in—in this case Story ID. That cell, and all the cells below it, then fill up with all the story ids known on that date. Notice that story id 15 is missing, and if you look at the original spreadsheet you’ll see that’s because that story was introduced only at a later date.
The next two columns contain formulas that are identical except for the last parameter:
and
Because all the formulas use the same data and the same date their outputs line up. So we can be sure (for example) that story id 4 (“Integrate with Muppex”) was estimated as size 1 on the date in question. But if you change the date to 26 June or later then you’ll see its estimate changes.
New function: Filter values for a given date
Just getting the data is often good enough. But sometimes we want a filtered view. For example you might want to see all stories that weren’t yet done on a given date. While you can put a spreadsheet filter on the getValid results you can now also use a new dedicated formula: filterValid. This effectively says “For a given date get all the values of a given field, but filtered down to just those where a certain condition is met”.
The parameters for filterValid are just the same as before, but there are two extra ones at the end:
- The date in question;
- Three parameters specifying the raw data: the area of the data (including the header row), the column name of the story ID, the column name of the date from which this row’s data is valid;
- The column name of the field we want to show;
- The column name of the field we want to test;
- The value that this field must be in order to pass the test.
For example, if we want to show only those stories that weren’t done on a given date then we might test the Is done? field for value 0. We can see this in the demo spreadsheet in the tab named Filtering example:
The two cells next to it again have exactly the same formula, except the fifth parameter is replaced each time to show a different field (again: the name and the estimate). Obviously you can show any field(s) you like. In the screenshot above you can see some stories are missing: they are the ones that have been done.
Tip: Extra fields for better filtering
Suppose we want to find “all stories that are in milestone 2 and not yet done”. If we’ve got a Milestone column and (of course) a Done? column then we can achieve this by adding a new column in our raw date with a calculation that simply says “milestone = 2 and done = true”. Then we can use filterValid to test the condition that this new column has the value True.
I’ve used this tool in a small project lasting a few weeks and just over 100 stories. Each story went through about two versions (“added” and “done”, or “added” and “descoped”). It worked very well.
Of course, no tool is perfect and everyone has their personal preferences. I would use a professionally-supported tool for anything much larger. But I find this spreadsheet, with its extra functions, is flexible and lightweight for small projects. I hope you find it useful, too.
References
niksilver.com 